﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Configuration;
using ARR.Entities;
using ARR.DataModel.Repositories;
using System.Data;
using ARR.Entities.Gateways;
using ARR.Shared;
using System.Data.SqlClient;
using System.Transactions;
using OfficeOpenXml;
using System.Diagnostics;
using ARR.Services.Domain.Reports;
using System.Reflection;


namespace ARR.Services.Domain.Importers
{
    //Importadores del Sistema Envoy
    public class ExcelImporter
    {
        private RangeReview rangeReview = null;

        private const string salesCorrectHeaderCSV = "bonus buy number;site;site name;article;article name;date (dd/mm/yyyy);article ean;tax rate;size;attribute1;attribute2;attribute3;attribute4;attribute5;manufacturer;brand;size of goods;subcategory code;subcategory name;uom;;sales units;sales;gross margin;sales units;sales;gross margin;gross sales;net margin;costs;costs w/vat";
        private const string salesCorrectHeader = "|bonus buy number|site|site name|article|article name|date (dd/mm/yyyy)|article ean|tax rate|size|attribute1|attribute2|attribute3|attribute4|attribute5|manufacturer|brand|size of goods|subcategory code|subcategory name|uom|sales units|sales|gross margin|sales units|sales|gross margin|gross sales|net margin|costs|costs w/vat";
        private const string articlesCorrectHeader = "|article|article ean|subcategory code|article name|manufact|sp w vat (|uom|type|site|ord|ean (order)|vc|t|pack|f|vendor|vendor.art.n|pp w/o va|per|oun|pp w/o vat|cur|net sp|tax rate|sprun|pricing site group|pl|safe.s|rpt|rp.prf|reor.p|max.st|pbc|pricing ref. artl|kp|brand|created date|size|attribute1|attribute2|attribute3|attribute4|attribute5|ranking|unidades|ventas|margen|margen %|margen % rr ant.|margen % teórico|acum. ventas|acum. skus|cobertura|cobertura tiendas|cobertura tiendas %|cobertura meses|cobertura meses %|unidades norm.|ventas norm.|share ventas %|share espacios %|gap ventas/espacios|status|categoría|subcategoría|inventario|width|facings|linear space mts|planograms";
        private const string storesCorrectHeader = "|site code|pbl|site name|region|subregion|state|city|address|phone|fax|mail|contact|site location|selcustomers|sellocation|shoppingoccasion|shop type|store format|size|layout|opening hours|sel|attribute1|attribute2";
        private const string piDiffsCorrectHeader = "|site|site name|pbl/mrn number|store type|store size|article|article name|merchandise category|category name|date (dd/mm/yyyy)|total bun|total cost|pi diff bun|pi diff cost|charge offs bun|charge offs cost|rebates";
        private const string inventoryCorrectHeader = "|date (dd/mm/yyyy)|article ean|site|site name|pbl/mrn number|article|article name|manufacturer|brand|size of goods|subcategory code|subcategory name|stock units|stock cost";
        private const string spacesCorrectHeader = "|store|store name|planogram|sap article id|uom|article description|mc|category|total_facings|width";
        private const string historyCorrectHeader = "|category code|category|universe|role|average volume|average sales|average gim|average % gim|average de-listed volume|average de-listed sales|average de-listed gim|same store sales contribution|same store gim contribution|same store ac. mg. contribution|same store volume cy|same store sales cy|same store gim cy|pi diff. cy|charge off cy|rebates cy|actual margin cy|same store % gim cy|pi diff. % cy|charge off % cy|rebates % cy|actual margin % cy|same store inventory cy|same store volume py|same store sales py|same store gim py|pi diff. py|charge off py|rebates py|actual margin py|same store % gim py|pi diff. % py|charge off % py|rebates % py|actual margin % py|same store inventory py|volume vs. py|sales vs. py|gim $ vs. py|pi diff. vs. py|charge off vs. py|rebates vs. py|actual margin vs. py|gim % vs. py|actual margin % vs. py|inventory vs. py|volume vs. avg. store|sales vs. avg. store|gim $ vs. avg. store|gim % vs. avg. store|space|gondola|transaction area|grab & go|wic|open cooler|freezer|forecourt|other exhibition|space share|annual stock turn|inventory days|gmroi|sales/ linear mt.|gmros|hurdle rate|average price|am - skus w/sales|am - new skus|am - total skus|skus in sales files|de-list proposal|after rr|10% sales|20% sales|30% sales|40% sales|50% sales|60% sales|70% sales|80% sales|90% sales|100% sales|# skus in the bottom 5%|# skus below hr|% skus with 80% sales|active categories|categories w/inv. analysis|pais|range";


        public ExcelImporter(RangeReview rangeReview)
        {
            this.rangeReview = rangeReview;

            var countryRow = CountriesRepository.Select(c => c.Id == rangeReview.CountryId).SingleOrDefault();
            rangeReview.Country = new Country(countryRow);
        }

        //Histórico
        public IList<ImporterResult> ImportHistoryStep1()
        {
            string sourceDirectoryPath = ImporterHelper.GetHistoryFilesPath();
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            if (Directory.Exists(sourceDirectoryPath))
            {
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                importerResults = ImporterHelper.ExcelImporterStep1Validations(historyCorrectHeader, "A6:CP6", files);
            }
            else
            {
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                    ErrorType = ErrorType.Error
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportHistoryStep2()
        {
            DateTime start = DateTime.Now;

            //LG: Meter culture como parámetro del Country 
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            ExcelRangeBase excelCell = null;
            int? rowNumber = 2;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales
                TempHistoryDetailsRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetHistoryFilesPath();
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempHistoryDetailRow>();
                DataRow row = null;
                decimal decimalValue = 0;

                #region Importo el historial de category assesment

                foreach (string file in files.Where(x => x.EndsWith("xlsx")))
                {
                    FileInfo fi = new FileInfo(file);
                    fileName = fi.Name;
                    ExcelPackage excel = new ExcelPackage(fi);

                    foreach (var sheet in excel.Workbook.Worksheets)
                    {
                        string header = string.Empty;
                        foreach (var cell in sheet.Cells["A6:CP6"])
                        {
                            if (cell.Value != null)
                            {
                                header += "|" + cell.Value.ToString().Trim().ToLower();
                            }
                        }

                        decimal exchangeRate = 0;
                        decimal spacesConversion = 0;
                        decimal fuelsConversion = 0;

                        if (sheet.Cells["D1"].Value != null && !string.IsNullOrWhiteSpace(sheet.Cells["D1"].Value.ToString()))
                        {
                            exchangeRate = decimal.Parse(sheet.Cells["D1"].Value.ToString());
                        }

                        if (sheet.Cells["D2"].Value != null && !string.IsNullOrWhiteSpace(sheet.Cells["D2"].Value.ToString()))
                        {
                            spacesConversion = decimal.Parse(sheet.Cells["D2"].Value.ToString());
                        }

                        if (sheet.Cells["D3"].Value != null && !string.IsNullOrWhiteSpace(sheet.Cells["D3"].Value.ToString()))
                        {
                            fuelsConversion = decimal.Parse(sheet.Cells["D3"].Value.ToString());
                        }

                        if (exchangeRate == 0 || spacesConversion == 0 || fuelsConversion == 0)
                        {
                            errorCount++;
                            importerResults.Add(new ImporterResult
                            {
                                Category = ResultCategory.PreRequisites,
                                Description = string.Format("Debe completar la tasa de cambio en la celda D1, la conversión de espacios a metros en la celda D2 y la conversión de Fuels en la celda D3 del Archivo: {0} - Hoja: {1}", fileName, sheet.Name),
                                ErrorType = ErrorType.Error,
                                Id = errorCount
                            });
                            break;
                        }

                        if (header == historyCorrectHeader)
                        {
                            bool processRow = true;

                            //Las celdas en nulo las saltea
                            foreach (var cell in sheet.Cells["A7:CP" + sheet.Dimension.End.Row]) //Solo proceso la hoja 1 del Excel
                            {
                                int column = cell.Start.Column;

                                if (excelCell != null && excelCell.Start.Row != cell.Start.Row)
                                {
                                    //Si la columna no es la A o si la columna es la A pero está en blanco
                                    if (column != 1)
                                    {
                                        processRow = false;
                                    }
                                    else
                                    {
                                        if (cell.Value == null || string.IsNullOrWhiteSpace(cell.Value.ToString()))
                                        {
                                            processRow = false;
                                        }
                                        else
                                        {
                                            processRow = true;
                                        }
                                    }
                                }

                                excelCell = cell;

                                switch (column)
                                {
                                    case 1:
                                        {
                                            if (processRow)
                                            {
                                                row = sourceTable.NewRow();
                                                sourceTable.Rows.Add(row);
                                                string categoryCode = cell.Value.ToString().Trim();
                                                if (categoryCode.Length > 1)
                                                {
                                                    categoryCode = categoryCode.PadLeft(6, '0');
                                                }
                                                row["CategoryCode"] = categoryCode;
                                                row["Rate"] = exchangeRate;
                                                row["SpacesConversion"] = spacesConversion;
                                                row["FuelsConversion"] = fuelsConversion;
                                            }
                                            rowNumber++;
                                            break;
                                        }
                                    case 2:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    row["Category"] = cell.Value.ToString().Trim();
                                                }
                                            }
                                            break;
                                        }
                                    case 3:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    row["Universe"] = cell.Value.ToString().Trim();
                                                }
                                            }
                                            break;
                                        }
                                    case 4:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    row["Role"] = cell.Value.ToString().Trim();
                                                }
                                            }
                                            break;
                                        }
                                    case 5:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["AverageVolume"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 6:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["AverageSales"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 7:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["AverageGIM"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 8:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["AverageGIMP"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 9:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["AverageDelistedVolume"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 10:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["AverageDelistedSales"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 11:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["AverageDelistedGIM"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 15:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_VolumeCY"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 16:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_SalesCY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 17:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_GIMCY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 18:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_PIDiffCY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 19:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_ChargeOffCY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 20:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_RebatesCY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 27:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_InventoryCY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 28:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_VolumePY"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 29:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_SalesPY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 30:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_GIMPY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 31:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_PIDiffPY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 32:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_ChargeOffPY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 33:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_RebatesPY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 40:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SS_InventoryPY"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 56:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Gondola"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 57:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["TransactionArea"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 58:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["GrabAndGo"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 59:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["WIC"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 60:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["OpenCooler"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 61:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Freezer"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 62:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Forecourt"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 63:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["OtherExhibition"] = decimalValue;
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 72:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SKUsWithSales"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 73:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["NewSKUs"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 75:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["SKUsInSalesFiles"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 76:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["DelistProposal"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 78:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Sales10"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 79:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Sales20"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 80:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Sales30"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 81:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Sales40"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 82:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Sales50"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 83:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Sales60"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 84:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Sales70"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 85:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Sales80"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 86:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Sales90"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 87:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Sales100"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 88:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["Bottom5"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 89:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["HurdleRate"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 91:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["ActiveCategories"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 92:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    if (decimal.TryParse(cell.Value.ToString(), out decimalValue))
                                                    {
                                                        row["CategoriesWithInvAnalysis"] = Convert.ToInt32(decimalValue);
                                                    }
                                                }
                                            }
                                            break;
                                        }
                                    case 93:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    row["Country"] = cell.Value.ToString().Trim();
                                                }
                                                else
                                                {
                                                    importerResults.Add(new ImporterResult
                                                    {
                                                        Category = ResultCategory.TemporalTable,
                                                        Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, cell.Worksheet.Name, cell.Address),
                                                        Description = "El dato del Pais es requerido. Completar para poder continuar.",
                                                        ErrorType = ErrorType.Error,
                                                        Id = errorCount
                                                    });
                                                }
                                            }
                                            break;
                                        }
                                    case 94:
                                        {
                                            if (processRow)
                                            {
                                                if (cell.Value != null)
                                                {
                                                    row["Range"] = cell.Value.ToString().Trim();
                                                }
                                            }
                                            break;
                                        }
                                }
                            }
                        }
                    }

                    DateTime end = DateTime.Now;
                    Debug.WriteLine("TIEMPO TOTAL: " + (end - start).TotalSeconds);
                }

                #endregion

                // new method: SQLBulkCopy:
                TempHistoryDetailsRepository.BulkCopy(sourceTable);

                //Actualizo los CategoryIds
                TempHistoryDetailsRepository.UpdateCategoryIds();

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.TemporalTable,
                    Description = Messages.ImportacionATablaTemporalCorrecta
                });
            }
            catch (Exception ex)
            {
                errorCount++;

                string sheetName = excelCell != null ? excelCell.Worksheet.Name : string.Empty;
                string cellAddress = excelCell != null ? excelCell.Address : string.Empty;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, sheetName, cellAddress),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportHistoryStep3()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;
            try
            {
                var history = TempHistoryDetailsRepository.Select();

                //Categorias no reconocidas
                foreach (var row in history.Where(s => s.CategoryCode.Length == 6 && !s.CategoryId.HasValue))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.CategoryNotFound, row.CategoryCode),
                        ErrorType = ErrorType.Error,
                        Id = count,
                        Workaround = Workaround.MissingCategory,
                        Param1 = row.CategoryCode,
                        Param2 = row.Category,
                        Param3 = row.Country,
                        Param4 = row.Range,
                        ItemId = row.Id
                    });
                }

                //Country no reconocido
                if (history.Count > 0)
                {
                    var row = history.First();
                    if (!CountriesRepository.Select(x => x.Name == row.Country).Any())
                    {
                        count++;
                        importerResults.Add(new ImporterResult
                        {
                            Category = ResultCategory.Validation,
                            Description = string.Format(Errors.CountryNotFound, row.Country),
                            ErrorType = ErrorType.Error,
                            Id = count,
                            Workaround = Workaround.MissingCountry,
                            Param1 = row.Country,
                            ItemId = row.Id
                        });
                    }
                }

                //Registros duplicados (mismo store, article, date)
                var duplicates =
                        from s in history
                        group s by new { s.CategoryCode, s.Country, s.Range } into g
                        where g.Count() > 1
                        select new
                        {
                            g.Key.CategoryCode,
                            g.Key.Country,
                            g.Key.Range,
                            Dups = g.Count()
                        };

                foreach (var row in duplicates.OrderBy(x => x.Country))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Errors.DuplicatedRows, row.CategoryCode),
                        ErrorType = ErrorType.Error, //Es unicamente un warning ya que despues se aplica el promedio de los registros encontrados.
                        Id = count,
                        Workaround = Workaround.Duplicated,
                        Param1 = row.CategoryCode,
                        Param2 = row.Country,
                        Param3 = row.Range
                    });
                }

            }
            catch (Exception ex)
            {
                count++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportHistoryStep4()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;

            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    //Hago Insert en la Tabla History y HistoryDetails
                    HistoryRepository.InsertFromTempHistoryDetails();

                    scope.Complete();
                }

                //Borro las tablas temporales
                TempHistoryDetailsRepository.TruncateTable();

                //Muevo Archivos importados a la carpeta old y Loggeo la importacion.
                string sourceDirectoryPath = ImporterHelper.GetHistoryFilesPath();
                ImporterHelper.MoveImportedFilesAndWriteLog(rangeReview.Country, sourceDirectoryPath);

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.Updating,
                    Description = Messages.HistoriaImportadaCorrectamente
                });
            }
            catch (Exception ex)
            {
                count++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Updating,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }

        //Espacios
        public IList<ImporterResult> ImportSpacesMasterStep1()
        {
            string sourceDirectoryPath = ImporterHelper.GetSpacesFilesPath(rangeReview.Country, rangeReview.Title);
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            if (Directory.Exists(sourceDirectoryPath))
            {
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                importerResults = ImporterHelper.ExcelImporterStep1Validations(spacesCorrectHeader, "A2:J2", files);
            }
            else
            {
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                    ErrorType = ErrorType.Error
                });
            }

            return importerResults;

            //IList<ImporterResult> importerResults = new List<ImporterResult>();

            //string fileName = null;
            //int? rowNumber = null;
            //int errorCount = 0;
            //try
            //{
            //    //Check Prerequisites
            //    //1. Chequeo que esté el archivo
            //    string sourceDirectoryPath = ImporterHelper.GetSpacesFilesPath(rangeReview.Country, rangeReview.Title);
            //    string[] files = Directory.GetFiles(sourceDirectoryPath);
            //    if (files.Length == 0)
            //    {
            //        errorCount++;
            //        importerResults.Add(new ImporterResult
            //        {
            //            Category = ResultCategory.PreRequisites,
            //            Description = Errors.NoFilesToImport,
            //            ErrorType = ErrorType.Error,
            //            Id = errorCount
            //        });
            //    }
            //    else
            //    {

            //        importerResults.Add(new ImporterResult
            //        {
            //            ErrorType = ErrorType.Information,
            //            Category = ResultCategory.PreRequisites,
            //            Description = Messages.ChequeoArchivosCorrecto
            //        });

            //        //2. Chequeo que los archivos sean xls o xlsx
            //        if (!files.Any(x => x.EndsWith("xlsx")))
            //        {
            //            errorCount++;
            //            importerResults.Add(new ImporterResult
            //            {
            //                Category = ResultCategory.PreRequisites,
            //                Description = Errors.ImporterErrorExcel2007Format,
            //                ErrorType = ErrorType.Error,
            //                Id = errorCount
            //            });
            //        }
            //        else
            //        {
            //            foreach (string file in files.Where(x => x.EndsWith("xlsx")))
            //            {
            //                ExcelPackage excel = new ExcelPackage(new FileInfo(file));
            //                if (excel != null && excel.Workbook != null)
            //                {
            //                    //Chequeo el formato
            //                    string header = string.Empty;

            //                    foreach (var cell in sheet.Cells["A2:J2"]) //Chequeo la primer hoja del excel
            //                    {
            //                        if (cell.Value != null)
            //                        {
            //                            header += "|" + cell.Value.ToString().Trim().ToLower();
            //                        }
            //                    }

            //                    if (header != spacesCorrectHeader)
            //                    {
            //                        errorCount++;
            //                        importerResults.Add(new ImporterResult
            //                        {
            //                            Category = ResultCategory.PreRequisites,
            //                            Description = "La planilla de espacios debe comenzar en la primer hoja del Excel en la celda A1 y tener las siguientes columnas: " + spacesCorrectHeader,
            //                            ErrorType = ErrorType.Error,
            //                            Id = errorCount
            //                        });
            //                    }
            //                    else
            //                    {
            //                        importerResults.Add(new ImporterResult
            //                        {
            //                            ErrorType = ErrorType.Information,
            //                            Category = ResultCategory.PreRequisites,
            //                            Description = string.Format(Messages.ChequeoCabeceraCorrecto, fileName)
            //                        });
            //                    }
            //                }
            //            }
            //        }


            //        //Si no hay ninguna tienda seleccionada como tienda modelo
            //        if (!RangeReviewStoresRepository.Select(x => x.RangeReviewId == rangeReview.Id && x.IsSameStore == true).Any())
            //        {
            //            errorCount++;
            //            importerResults.Add(new ImporterResult
            //            {
            //                Category = ResultCategory.PreRequisites,
            //                Description = Errors.ImporterErrorSpacesWithoutSameStore,
            //                ErrorType = ErrorType.Error,
            //                Id = errorCount
            //            });
            //        }
            //    }
            //}
            //catch (Exception ex)
            //{
            //    errorCount++;
            //    importerResults.Add(new ImporterResult
            //    {
            //        Category = ResultCategory.PreRequisites,
            //        Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
            //        Description = ex.Message,
            //        ErrorType = ErrorType.Error,
            //        Id = errorCount
            //    });
            //}

            //return importerResults;
        }

        public IList<ImporterResult> ImportSpacesMasterStep2()
        {
            //LG: Meter culture como parámetro del Country 
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            ExcelRangeBase excelCell = null;
            int? rowNumber = 2;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempSpaces]
                TempSpacesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetSpacesFilesPath(rangeReview.Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempSpaceRow>();

                var sameStoreRow = RangeReviewStoresRepository.Select(x => x.RangeReviewId == rangeReview.Id && x.IsSameStore == true).FirstOrDefault();


                if (sameStoreRow != null)
                {
                    RangeReviewStore sameStore = new RangeReviewStore(sameStoreRow);
                    string sameStoreCode = StoresRepository.Select(x => x.Id == sameStore.StoreId).FirstOrDefault().Code;

                    foreach (string file in files.Where(x => x.EndsWith("xlsx")))
                    {
                        FileInfo fi = new FileInfo(file);
                        fileName = fi.Name;
                        ExcelPackage excel = new ExcelPackage(fi);
                        DataRow row = null;

                        foreach (var sheet in excel.Workbook.Worksheets)
                        {
                            string header = string.Empty;
                            foreach (var cell in sheet.Cells["A2:J2"]) //Chequeo la primer hoja del excel
                            {
                                if (cell.Value != null)
                                {
                                    header += "|" + cell.Value.ToString().Trim().ToLower();
                                }
                            }

                            if (header == spacesCorrectHeader)
                            {

                                //Las celdas en nulo las saltea
                                bool include = false;
                                foreach (var cell in sheet.Cells["A3:J" + sheet.Dimension.End.Row]) //Solo proceso la hoja 1 del Excel
                                {
                                    excelCell = cell;

                                    string column = cell.Address.Substring(0, 1);
                                    if (column == "A")
                                    {
                                        if (cell.Value.ToString() == sameStoreCode)
                                        {
                                            include = true;
                                        }
                                        else
                                        {
                                            include = false;
                                        }
                                    }

                                    if (include)
                                    {
                                        switch (column)
                                        {
                                            case "A":
                                                row = sourceTable.NewRow();
                                                sourceTable.Rows.Add(row);
                                                rowNumber++;
                                                row[2] = cell.Value; //StoreCode
                                                break;
                                            case "B":
                                                row[3] = cell.Value; //StoreName
                                                break;
                                            case "C":
                                                row[6] = cell.Value; //Planogram
                                                break;
                                            case "D":
                                                row[7] = cell.Value.ToString(); //ArticleCode
                                                break;
                                            case "E":
                                                row[8] = cell.Value; //UOM
                                                break;
                                            case "F":
                                                row[9] = cell.Value; //ArticleName
                                                break;
                                            case "G":
                                                row[10] = cell.Value.ToString().PadLeft(6, '0'); //MC
                                                break;
                                            case "H":
                                                row[11] = cell.Value; //Category
                                                break;
                                            case "I":
                                                row[12] = cell.Value; //Facings Ver si conviene usar un try parse, si no viene un int pincha
                                                break;
                                            case "J":
                                                row[13] = cell.Value; //Width Ver si conviene usar un try parse, si no viene un decimal pincha
                                                break;
                                        }
                                    }
                                }
                            }
                        }

                        // new method: SQLBulkCopy:
                        TempSpacesRepository.BulkCopy(sourceTable);

                        //Actualizo los Ids (Stores, Articles y Planograms)
                        TempSpacesRepository.UpdateIds(rangeReview.Id);

                        importerResults.Add(new ImporterResult
                        {
                            ErrorType = ErrorType.Information,
                            Category = ResultCategory.TemporalTable,
                            Description = Messages.ImportacionATablaTemporalCorrecta
                        });
                    }
                }
                else
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.TemporalTable,
                        Description = string.Format("No existe SameStore configurado para {0}", rangeReview.Title),
                        ErrorType = ErrorType.Error,
                        Id = errorCount
                    });
                }
            }
            catch (Exception ex)
            {
                errorCount++;

                string sheetName = excelCell != null ? excelCell.Worksheet.Name : string.Empty;
                string cellAddress = excelCell != null ? excelCell.Address : string.Empty;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, sheetName, cellAddress),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportSpacesMasterStep3()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;
            try
            {
                var spaces = TempSpacesRepository.Select();
                var missingArticles = from s in spaces
                                      where (s.ArticleId == null || s.ArticleId.Value == 0)
                                      group s by new { s.ArticleCode, s.UOM, s.ArticleName } into g
                                      select new { g.Key.ArticleCode, g.Key.UOM, g.Key.ArticleName };

                //Registros sin ArticleId. Los agrego como en el caso de inventarios y ventas por eso lo muestro como Warning
                foreach (var space in missingArticles)
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.RegistroSinArticleId, space.ArticleCode),
                        ErrorType = ErrorType.Warning,
                        Id = count,
                        Workaround = Workaround.MissingArticleId,
                        Param1 = space.ArticleCode,
                        Param2 = space.UOM,
                        Param3 = space.ArticleName
                    });
                }

                //Registros sin StoreId
                foreach (var space in spaces.Where(s => s.StoreId == null || s.StoreId.Value == 0))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.RegistroSinStoreId, space.Id),
                        ErrorType = ErrorType.Error, //TODO: Despues pasarlo a ERROR
                        Id = count,
                        Workaround = Workaround.MissingStoreId,
                        Param1 = space.StoreCode,
                        Param2 = space.StoreName,
                        Param3 = space.ArticleCode,
                        Param4 = space.ArticleName,
                        Param5 = space.Planogram,
                        ItemId = space.Id
                    });
                }


                //Anchos <= 0
                foreach (var space in spaces.Where(s => s.Width <= 0))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format("El ancho debe ser mayor a 0. {0} {1} {2} {3} {4} {5}", space.StoreCode, space.StoreName, space.Planogram, space.ArticleCode, space.UOM, space.ArticleName),
                        ErrorType = ErrorType.Error,
                        Id = count,
                        Workaround = Workaround.WidthIsZero,
                        Param1 = space.StoreCode,
                        Param2 = space.StoreName,
                        Param3 = space.ArticleCode,
                        Param4 = space.ArticleName,
                        Param5 = space.Planogram,
                        ItemId = space.Id
                    });
                }

                //Facings en nulo o menor o igual a 0
                foreach (var space in spaces.Where(s => s.Facings.HasValue == false || s.Facings.Value <= 0))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format("La cantidad de Facings debe ser mayor a 0. {0} {1} {2} {3} {4} {5}", space.StoreCode, space.StoreName, space.Planogram, space.ArticleCode, space.UOM, space.ArticleName),
                        ErrorType = ErrorType.Warning,
                        Id = count,
                        Workaround = Workaround.WidthIsZero,
                        Param1 = space.StoreCode,
                        Param2 = space.StoreName,
                        Param3 = space.ArticleCode,
                        Param4 = space.ArticleName,
                        Param5 = space.Planogram,
                        ItemId = space.Id
                    });
                }

                //Registros duplicados (mismo store, article, planogram y distintos facings)
                var duplicates = (from s in
                                     (
                                         from s in spaces
                                         where s.ArticleId.HasValue
                                         group s by new { s.StoreCode, s.StoreName, s.ArticleCode, s.UOM, s.Planogram, s.Facings } into g
                                         select new
                                         {
                                             g.Key.StoreCode,
                                             g.Key.StoreName,
                                             g.Key.ArticleCode,
                                             g.Key.UOM,
                                             g.Key.Planogram,
                                             g.Key.Facings
                                         })
                                 group s by new { s.StoreCode, s.StoreName, s.ArticleCode, s.UOM, s.Planogram, s.Facings } into g
                                 select new
                                 {
                                     g.Key.StoreCode,
                                     g.Key.StoreName,
                                     g.Key.ArticleCode,
                                     g.Key.UOM,
                                     g.Key.Planogram,
                                     g.Key.Facings,
                                     Dups = g.Count()
                                 }).Where(x => x.Dups > 1);

                foreach (var space in duplicates.OrderBy(x => x.ArticleCode))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.EspaciosDuplicados, space.Dups, space.StoreCode, space.ArticleCode, space.Planogram),
                        ErrorType = ErrorType.Error,
                        Id = count,
                        Workaround = Workaround.Duplicated,
                        Param1 = space.StoreCode,
                        Param2 = space.StoreName,
                        Param3 = space.ArticleCode,
                        Param4 = space.UOM,
                        Param5 = space.Planogram,
                    });
                }

                //Mismo articulo con 2 anchos
                var dupWidths =
                                from d in
                                    (
                                        from s in spaces
                                        group s by new { s.ArticleCode, s.UOM, s.Width } into g
                                        select new
                                        {
                                            g.Key.ArticleCode
                                            ,
                                            g.Key.UOM
                                            ,
                                            g.Key.Width
                                        })
                                group d by new { d.ArticleCode, d.UOM } into g
                                where g.Count() > 1
                                select new
                                {
                                    g.Key.ArticleCode,
                                    g.Key.UOM,
                                    Dups = g.Count()
                                };

                foreach (var space in dupWidths.OrderBy(x => x.ArticleCode))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.EspaciosConAnchosDistintos, space.Dups, space.ArticleCode, space.UOM),
                        ErrorType = ErrorType.Error, //TODO: Despues pasarlo a ERROR
                        Id = count,
                        Workaround = Workaround.Duplicated,
                        Param1 = space.ArticleCode,
                        Param2 = space.UOM,
                        Param3 = space.Dups.ToString(),
                    });
                }

                //Mostrar a titulo informativo los totales por planograma
                var totals = from s in spaces
                             group s by new { s.StoreCode, s.StoreName, s.Planogram } into g
                             select new
                             {
                                 g.Key.StoreCode
                                ,
                                 g.Key.StoreName
                                ,
                                 g.Key.Planogram
                                ,
                                 TotalWidth = g.Sum(x => x.Facings * x.Width)
                             };

                foreach (var space in totals.OrderBy(x => x.StoreCode).ThenBy(x => x.Planogram))
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format("Store {0} - Planograma {1} = {2}", space.StoreName, space.Planogram, space.TotalWidth),
                        ErrorType = ErrorType.Information,
                        Workaround = Workaround.SpacesTotals,
                        Param1 = space.StoreCode,
                        Param2 = space.StoreName,
                        Param3 = space.Planogram,
                        Param4 = space.TotalWidth.HasValue ? space.TotalWidth.ToString() : string.Empty
                    });
                }

                //TODO: validar si el Article en el maestro ya tiene un ancho y el que se está informando ahora difiere. Aca el usuario deberia elegir entre quedarse con el actual o reemplazarlo

            }
            catch (Exception ex)
            {
                count++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportSpacesMasterStep4()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;

            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    //Hago Insert de los SKUs que no estaban
                    ArticlesRepository.InsertFromTempSpaces(rangeReview.Id);

                    //Hago Insert en la Tabla Planograms
                    PlanogramsRepository.InsertFromTempSpaces(rangeReview.Id);

                    //Hago Insert en la Tabla Spaces
                    SpacesRepository.InsertFromTempSpaces(rangeReview.Id);

                    //Hago Update en la Tabla Articles calculando el Total Facings y poniendo el Width
                    ArticlesRepository.UpdateSpaces(rangeReview.Id);

                    scope.Complete();
                }

                //Borro las tablas temporales
                TempSpacesRepository.TruncateTable();

                //Muevo Archivos importados a la carpeta old y Loggeo la importacion.
                string sourceDirectoryPath = ImporterHelper.GetSpacesFilesPath(rangeReview.Country, rangeReview.Title);
                ImporterHelper.MoveImportedFilesAndWriteLog(rangeReview.Country, sourceDirectoryPath);

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.Updating,
                    Description = Messages.EspaciosImportadosCorrectamente
                });
            }
            catch (Exception ex)
            {
                count++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Updating,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }


        //Inventarios
        public IList<ImporterResult> ImportInventoryMasterStep1()
        {
            string sourceDirectoryPath = ImporterHelper.GetInventoryMasterFilePath(rangeReview.Country, rangeReview.Title);
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            if (Directory.Exists(sourceDirectoryPath))
            {
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                importerResults = ImporterHelper.ExcelImporterStep1Validations(inventoryCorrectHeader, "A2:N2", files);
            }
            else
            {
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                    ErrorType = ErrorType.Error
                });
            }

            return importerResults;

            //IList<ImporterResult> importerResults = new List<ImporterResult>();

            //string fileName = null;
            //int? rowNumber = null;
            //int errorCount = 0;
            //try
            //{
            //    //Check Prerequisites
            //    //1. Chequeo que esté el archivo
            //    string sourceDirectoryPath = ImporterHelper.GetInventoryMasterFilePath(rangeReview.Country, rangeReview.Title);
            //    string[] files = Directory.GetFiles(sourceDirectoryPath);
            //    if (files.Length == 0)
            //    {
            //        errorCount++;
            //        importerResults.Add(new ImporterResult
            //        {
            //            Category = ResultCategory.PreRequisites,
            //            Description = Errors.NoFilesToImport,
            //            ErrorType = ErrorType.Error,
            //            Id = errorCount
            //        });
            //    }
            //    else
            //    {

            //        importerResults.Add(new ImporterResult
            //        {
            //            ErrorType = ErrorType.Information,
            //            Category = ResultCategory.PreRequisites,
            //            Description = Messages.ChequeoArchivosCorrecto
            //        });

            //        //2. Chequeo que los archivos sean xls o xlsx
            //        if (!files.Any(x => x.EndsWith("xlsx")))
            //        {
            //            errorCount++;
            //            importerResults.Add(new ImporterResult
            //            {
            //                Category = ResultCategory.PreRequisites,
            //                Description = Errors.ImporterErrorExcel2007Format,
            //                ErrorType = ErrorType.Error,
            //                Id = errorCount
            //            });
            //        }
            //        else
            //        {
            //            foreach (string file in files.Where(x => x.EndsWith("xlsx")))
            //            {
            //                ExcelPackage excel = new ExcelPackage(new FileInfo(file));
            //                if (excel != null && excel.Workbook != null)
            //                {
            //                    //Chequeo el formato
            //                    string header = string.Empty;

            //                    foreach (var cell in excel.Workbook.Worksheets[1].Cells["A2:M2"]) //Chequeo la primer hoja del excel
            //                    {
            //                        if (cell.Value != null)
            //                        {
            //                            header += "|" + cell.Value.ToString().Trim().ToLower();
            //                        }
            //                    }

            //                    if (header != inventoryCorrectHeader)
            //                    {
            //                        errorCount++;
            //                        importerResults.Add(new ImporterResult
            //                        {
            //                            Category = ResultCategory.PreRequisites,
            //                            Description = "La planilla de inventarios debe comenzar en la primer hoja del Excel en la celda A1 y tener las siguientes columnas: " + inventoryCorrectHeader,
            //                            ErrorType = ErrorType.Error,
            //                            Id = errorCount
            //                        });
            //                    }
            //                    else
            //                    {
            //                        importerResults.Add(new ImporterResult
            //                        {
            //                            ErrorType = ErrorType.Information,
            //                            Category = ResultCategory.PreRequisites,
            //                            Description = string.Format(Messages.ChequeoCabeceraCorrecto, fileName)
            //                        });
            //                    }
            //                }
            //            }
            //        }

            //    }
            //}
            //catch (Exception ex)
            //{
            //    errorCount++;
            //    importerResults.Add(new ImporterResult
            //    {
            //        Category = ResultCategory.PreRequisites,
            //        Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
            //        Description = ex.Message,
            //        ErrorType = ErrorType.Error,
            //        Id = errorCount
            //    });
            //}

            //return importerResults;
        }

        public IList<ImporterResult> ImportInventoryMasterStep2()
        {
            DateTime start = DateTime.Now;

            //LG: Meter culture como parámetro del Country 
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            ExcelRangeBase excelCell = null;
            int? rowNumber = 2;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempInventory]
                TempInventoriesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetInventoryMasterFilePath(rangeReview.Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempInventoryRow>();
                List<TempInventoryRow> tempInventories = new List<TempInventoryRow>();
                TempInventoryRow row = null;
                decimal quantity;
                decimal cost;

                #region Importo los inventarios

                foreach (string file in files.Where(x => x.EndsWith("xlsx")))
                {
                    tempInventories = new List<TempInventoryRow>();

                    FileInfo fi = new FileInfo(file);
                    fileName = fi.Name;
                    ExcelPackage excel = new ExcelPackage(fi);

                    foreach (var sheet in excel.Workbook.Worksheets)
                    {
                        string header = string.Empty;
                        foreach (var cell in sheet.Cells["A2:N2"]) //Chequeo la primer hoja del excel
                        {
                            if (cell.Value != null)
                            {
                                header += "|" + cell.Value.ToString().Trim().ToLower();
                            }
                        }

                        if (header == inventoryCorrectHeader)
                        {
                            bool processRow = true;

                            //Las celdas en nulo las saltea
                            foreach (var cell in sheet.Cells["A3:N" + sheet.Dimension.End.Row]) //Solo proceso la hoja 1 del Excel
                            {
                                string column = cell.Address.Substring(0, 1);

                                if (excelCell != null && excelCell.Start.Row != cell.Start.Row)
                                {
                                    //Si la columna no es la A o si la columna es la A pero está en blanco
                                    if (column != "A")
                                    {
                                        processRow = false;
                                    }
                                    else
                                    {
                                        if (cell.Value == null || string.IsNullOrWhiteSpace(cell.Value.ToString()))
                                        {
                                            processRow = false;
                                        }
                                        else
                                        {
                                            processRow = true;
                                        }
                                    }
                                }

                                excelCell = cell;

                                switch (column)
                                {
                                    case "A":
                                        {
                                            if (processRow)
                                            {
                                                row = new TempInventoryRow();
                                                tempInventories.Add(row);
                                                row.Date = DateTime.Parse(cell.Value.ToString());
                                            }
                                            rowNumber++;
                                            break;
                                        }
                                    case "B":
                                        {
                                            if (processRow)
                                            {
                                                row.EAN = cell.Value.ToString();
                                            }
                                            break;
                                        }
                                    case "C":
                                        {
                                            if (processRow)
                                            {
                                                row.StoreCode = cell.Value.ToString();
                                            }
                                            break;
                                        }
                                    case "F":
                                        {
                                            if (processRow)
                                            {
                                                row.ArticleCode = cell.Value.ToString().Trim();
                                            }
                                            break;
                                        }
                                    case "G":
                                        {
                                            if (processRow)
                                            {
                                                row.Description = cell.Value.ToString();
                                            }
                                            break;
                                        }
                                    case "H":
                                        {
                                            if (processRow)
                                            {
                                                row.Manufacturer = cell.Value.ToString();
                                            }
                                            break;
                                        }
                                    case "I":
                                        {
                                            if (processRow)
                                            {
                                                row.Brand = cell.Value.ToString();
                                            }
                                            break;
                                        }
                                    case "K":
                                        {
                                            if (processRow)
                                            {
                                                row.CategoryCode = cell.Value.ToString();
                                            }
                                            break;
                                        }
                                    case "M":
                                        {
                                            if (processRow)
                                            {
                                                decimal.TryParse(cell.Value.ToString(), out quantity);

                                                if (string.IsNullOrWhiteSpace(cell.Value.ToString()))
                                                    row.Quantity = null;
                                                else
                                                    row.Quantity = quantity;
                                            }
                                            break;
                                        }
                                    case "N":
                                        {
                                            if (processRow)
                                            {
                                                decimal.TryParse(cell.Value.ToString(), out cost);

                                                if (string.IsNullOrWhiteSpace(cell.Value.ToString()))
                                                    row.Cost = null;
                                                else
                                                    row.Cost = cost;
                                            }
                                            break;
                                        }
                                }
                            }
                        }
                    }

                    DateTime end = DateTime.Now;
                    Debug.WriteLine("TIEMPO TOTAL: " + (end - start).TotalSeconds);
                    
                    #region Sumarizo y Genero DataTable

                    sourceTable = null;
                    sourceTable = ImporterHelper.CreateDataTableFromGateway<TempInventoryRow>();

                    var q = from inv in tempInventories
                            group inv by new { inv.Date, inv.StoreCode, inv.ArticleCode, inv.Description, inv.CategoryCode, inv.EAN, inv.Brand, inv.Manufacturer } into g
                            select new
                            {
                                Date = g.Key.Date,
                                StoreCode = g.Key.StoreCode,
                                ArticleCode = g.Key.ArticleCode,
                                Description = g.Key.Description,
                                CategoryCode = g.Key.CategoryCode,
                                EAN = g.Key.EAN,
                                Manufacturer = g.Key.Manufacturer,
                                Brand = g.Key.Brand,
                                Quantity = g.Average(x => x.Quantity),
                                Cost = g.Average(x => x.Cost)
                            };


                    foreach (var inv in q)
                    {
                        DataRow datarow = sourceTable.NewRow();

                        datarow["Date"] = inv.Date;
                        datarow["StoreCode"] = inv.StoreCode;
                        datarow["ArticleCode"] = inv.ArticleCode;
                        datarow["Description"] = inv.Description;
                        datarow["CategoryCode"] = inv.CategoryCode;
                        datarow["EAN"] = inv.EAN;
                        datarow["Brand"] = inv.Brand;
                        datarow["Manufacturer"] = inv.Manufacturer;

                        if (inv.Quantity.HasValue)
                            datarow["Quantity"] = inv.Quantity;
                        else
                            datarow["Quantity"] = DBNull.Value;

                        if (inv.Cost.HasValue)
                            datarow["Cost"] = inv.Cost;
                        else
                            datarow["Cost"] = DBNull.Value;

                        sourceTable.Rows.Add(datarow);
                    }
                    #endregion
                    
                    TempInventoriesRepository.BulkCopy(sourceTable);
                                        
                }

                #endregion


                //Actualizo los Ids (Stores, Articles)
                TempInventoriesRepository.UpdateIds(rangeReview.Id);

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.TemporalTable,
                    Description = Messages.ImportacionATablaTemporalCorrecta
                });
            }
            catch (Exception ex)
            {
                errorCount++;

                string sheetName = excelCell != null ? excelCell.Worksheet.Name : string.Empty;
                string cellAddress = excelCell != null ? excelCell.Address : string.Empty;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, sheetName, cellAddress),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }


        //PI Differences
        public IList<ImporterResult> ImportPIDifferencesStep1()
        {
            string sourceDirectoryPath = ImporterHelper.GetPIDifferencesFilePath(rangeReview.Country, rangeReview.Title);
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            if (Directory.Exists(sourceDirectoryPath))
            {
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                importerResults = ImporterHelper.ExcelImporterStep1Validations(piDiffsCorrectHeader, "A2:Q2", files);
            }
            else
            {
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                    ErrorType = ErrorType.Error
                });
            }
            return importerResults;
        }

        public IList<ImporterResult> ImportPIDifferencesStep2()
        {
            DateTime start = DateTime.Now;

            //LG: Meter culture como parámetro del Country 
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            ExcelRangeBase excelCell = null;
            int? rowNumber = 2;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempInventory]
                TempPIDifferencesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetPIDifferencesFilePath(rangeReview.Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempPIDifferenceRow>();


                foreach (string file in files.Where(x => x.EndsWith("xlsx")))
                {
                    FileInfo fi = new FileInfo(file);
                    fileName = fi.Name;
                    ExcelPackage excel = new ExcelPackage(fi);
                    DataRow row = null;

                    foreach (var sheet in excel.Workbook.Worksheets)
                    {
                        string header = string.Empty;
                        foreach (var cell in sheet.Cells["A2:Q2"]) //Chequeo la primer hoja del excel
                        {
                            if (cell.Value != null)
                            {
                                header += "|" + cell.Value.ToString().Trim().ToLower();
                            }
                        }

                        if (header == piDiffsCorrectHeader)
                        {

                            //Las celdas en nulo las saltea
                            foreach (var cell in sheet.Cells["A3:Q" + sheet.Dimension.End.Row]) //Solo proceso la hoja 1 del Excel
                            {
                                excelCell = cell;

                                string column = cell.Address.Substring(0, 1);

                                switch (column)
                                {
                                    case "A":
                                        row = sourceTable.NewRow();
                                        sourceTable.Rows.Add(row);
                                        rowNumber++;
                                        row["StoreCode"] = cell.Value;
                                        break;
                                    case "F":
                                        {
                                            row["ArticleCode"] = cell.Value.ToString().Trim();
                                            break;
                                        }
                                    case "J":
                                        {
                                            row["Date"] = cell.Value;
                                            break;
                                        }
                                    case "M":
                                        row["PIDiffUnits"] = cell.Value;
                                        break;
                                    case "N":
                                        row["PIDiff"] = cell.Value;
                                        break;
                                    case "O":
                                        row["ChargeOffsUnits"] = cell.Value;
                                        break;
                                    case "P":
                                        row["ChargeOffs"] = cell.Value;
                                        break;
                                    case "Q":
                                        row["Rebates"] = cell.Value;
                                        break;
                                }
                            }
                        }
                    }


                    DateTime end = DateTime.Now;
                    Debug.WriteLine("TIEMPO TOTAL: " + (end - start).TotalSeconds);
                }


                // new method: SQLBulkCopy:
                TempPIDifferencesRepository.BulkCopy(sourceTable);

                //Actualizo los Ids (Stores, Articles)
                TempPIDifferencesRepository.UpdateIds(rangeReview.Id);

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.TemporalTable,
                    Description = Messages.ImportacionATablaTemporalCorrecta
                });
            }
            catch (Exception ex)
            {
                errorCount++;

                string sheetName = excelCell != null ? excelCell.Worksheet.Name : string.Empty;
                string cellAddress = excelCell != null ? excelCell.Address : string.Empty;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, sheetName, cellAddress),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportPIDifferencesStep3()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;
            try
            {
                var piDiffs = TempPIDifferencesRepository.Select();

                //Registros sin ArticleId
                foreach (var diff in piDiffs.Where(s => s.ArticleId == null || s.ArticleId.Value == 0))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.RegistroSinArticleId, diff.Id),
                        ErrorType = ErrorType.Warning,
                        Id = count,
                        Workaround = Workaround.MissingArticleId,
                        Param1 = diff.StoreCode,
                        Param2 = diff.ArticleCode,
                        Param3 = diff.Date.ToString("yyyyMM"),
                        ItemId = diff.Id
                    });
                }

                //Registros sin StoreId
                foreach (var diff in piDiffs.Where(s => s.StoreId == null || s.StoreId.Value == 0))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.RegistroSinStoreId, diff.Id),
                        ErrorType = ErrorType.Error,
                        Id = count,
                        Workaround = Workaround.MissingStoreId,
                        Param1 = diff.StoreCode,
                        Param2 = diff.ArticleCode,
                        Param3 = diff.Date.ToString("yyyyMM"),
                        ItemId = diff.Id
                    });
                }

                //Registros duplicados (mismo store, article, date)
                var duplicates =
                        from s in piDiffs
                        group s by new { s.StoreCode, s.Date, s.ArticleCode } into g
                        where g.Count() > 1
                        select new
                        {
                            g.Key.StoreCode,
                            g.Key.ArticleCode,
                            g.Key.Date,
                            Dups = g.Count()
                        };

                foreach (var diff in duplicates.OrderBy(x => x.ArticleCode))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.PIDifferencesDuplicados, diff.Dups, diff.StoreCode, diff.ArticleCode, diff.Date),
                        ErrorType = ErrorType.Warning, //Es unicamente un warning ya que despues se aplica el promedio de los registros encontrados.
                        Id = count,
                        Workaround = Workaround.Duplicated,
                        Param1 = diff.StoreCode,
                        Param2 = diff.ArticleCode,
                        Param3 = diff.Date.ToString("yyyyMM")
                    });
                }

                if (importerResults.Any(x => x.Workaround == Workaround.Duplicated))
                {
                    count++;
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.Validation,
                        Description = string.Format(Messages.SePromediaranLosRepetidos),
                        ErrorType = ErrorType.Warning,
                        Id = count,
                        Workaround = Workaround.Duplicated
                    });
                }

            }
            catch (Exception ex)
            {
                count++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Validation,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportPIDifferencesStep4()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();
            int count = 0;

            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    //Hago Update del Maestro de Inventarios
                    ArticleSalesRepository.UpdateFromTempPIDifferences();

                    scope.Complete();
                }

                //Borro las tablas temporales
                TempPIDifferencesRepository.TruncateTable();

                //Muevo Archivos importados a la carpeta old y Loggeo la importacion.
                string sourceDirectoryPath = ImporterHelper.GetPIDifferencesFilePath(rangeReview.Country, rangeReview.Title);
                ImporterHelper.MoveImportedFilesAndWriteLog(rangeReview.Country, sourceDirectoryPath);

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.Updating,
                    Description = Messages.LasDiferenciaDeInventarioSeImportaronCorrectamente
                });

            }
            catch (Exception ex)
            {
                count++;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.Updating,
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = count
                });
            }

            return importerResults;
        }


        //Tiendas
        public IList<ImporterResult> ImportStoreMasterStep1()
        {
            string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(rangeReview.Country, rangeReview.Title);
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            if (Directory.Exists(sourceDirectoryPath))
            {
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                importerResults = ImporterHelper.ExcelImporterStep1Validations(storesCorrectHeader, "A2:X2", files);
            }
            else
            {
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                    ErrorType = ErrorType.Error
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportStoreMasterStep2()
        {
            DateTime start = DateTime.Now;

            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            ExcelRangeBase excelCell = null;
            int? rowNumber = 2;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempInventory]
                TempStoresRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetStoreMasterFilePath(rangeReview.Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempStore>();


                foreach (string file in files.Where(x => x.EndsWith("xlsx")))
                {
                    FileInfo fi = new FileInfo(file);
                    fileName = fi.Name;
                    ExcelPackage excel = new ExcelPackage(fi);
                    DataRow row = null;

                    foreach (var sheet in excel.Workbook.Worksheets)
                    {
                        string header = string.Empty;
                        foreach (var cell in sheet.Cells["A2:X2"]) //Chequeo la primer hoja del excel
                        {
                            if (cell.Value != null)
                            {
                                header += "|" + cell.Value.ToString().Trim().ToLower();
                            }
                        }

                        if (header == storesCorrectHeader)
                        {

                            //Las celdas en nulo las saltea
                            foreach (var cell in sheet.Cells["A3:X" + sheet.Dimension.End.Row]) //Solo proceso la hoja 1 del Excel
                            {
                                excelCell = cell;

                                string column = cell.Address.Substring(0, 1);

                                switch (column)
                                {
                                    case "A":
                                        {
                                            row = sourceTable.NewRow();
                                            sourceTable.Rows.Add(row);
                                            rowNumber++;
                                            row["Code"] = cell.Value.ToString().PadLeft(4, '0');
                                            row["CountryId"] = rangeReview.CountryId;
                                            break;
                                        }
                                    case "B":
                                        {
                                            row["PBL"] = cell.Value;
                                            break;
                                        }
                                    case "C":
                                        {
                                            row["Name"] = cell.Value;
                                            break;
                                        }
                                    case "D":
                                        {
                                            row["Region"] = cell.Value;
                                            break;
                                        }

                                    case "E":
                                        {
                                            row["Subregion"] = cell.Value;
                                            break;
                                        }
                                    case "F":
                                        {
                                            row["State"] = cell.Value;
                                            break;
                                        }
                                    case "G":
                                        {
                                            row["City"] = cell.Value;
                                            break;
                                        }
                                    case "H":
                                        {
                                            row["Address"] = cell.Value;
                                            break;
                                        }
                                    case "I":
                                        {
                                            row["Phone"] = cell.Value;
                                            break;
                                        }
                                    case "J":
                                        {
                                            row["Fax"] = cell.Value;
                                            break;
                                        }
                                    case "K":
                                        {
                                            row["Mail"] = cell.Value;
                                            break;
                                        }
                                    case "L":
                                        {
                                            row["Contact"] = cell.Value;
                                            break;
                                        }
                                    case "M":
                                        {
                                            row["SiteLocation"] = cell.Value;
                                            break;
                                        }
                                    case "N":
                                        {
                                            row["SELCustomers"] = cell.Value;
                                            break;
                                        }
                                    case "O":
                                        {
                                            row["SELLocation"] = cell.Value;
                                            break;
                                        }
                                    case "P":
                                        {
                                            row["ShoppingOccasion"] = cell.Value;
                                            break;
                                        }
                                    case "Q":
                                        {
                                            row["ShopType"] = cell.Value;
                                            break;
                                        }
                                    case "R":
                                        {
                                            row["Format"] = cell.Value;
                                            break;
                                        }
                                    case "S":
                                        {
                                            row["Size"] = cell.Value;
                                            break;
                                        }
                                    case "T":
                                        {
                                            row["Layout"] = cell.Value;
                                            break;
                                        }
                                    case "U":
                                        {
                                            row["OpeningHours"] = cell.Value;
                                            break;
                                        }
                                    case "V":
                                        {
                                            row["SEL"] = cell.Value;
                                            break;
                                        }
                                    case "W":
                                        {
                                            row["Attribute1"] = cell.Value;
                                            break;
                                        }
                                    case "X":
                                        {
                                            row["Attribute2"] = cell.Value;
                                            break;
                                        }
                                }
                            }
                        }
                    }


                    DateTime end = DateTime.Now;
                    Debug.WriteLine("TIEMPO TOTAL: " + (end - start).TotalSeconds);

                    // new method: SQLBulkCopy:
                    TempStoresRepository.BulkCopy(sourceTable);

                }

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.TemporalTable,
                    Description = Messages.ImportacionATablaTemporalCorrecta
                });
            }
            catch (Exception ex)
            {
                errorCount++;

                string sheetName = excelCell != null ? excelCell.Worksheet.Name : string.Empty;
                string cellAddress = excelCell != null ? excelCell.Address : string.Empty;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, sheetName, cellAddress),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }


        //Articulos
        public IList<ImporterResult> ImportArticleMasterStep1()
        {
            string sourceDirectoryPath = ImporterHelper.GetArticleMasterFilePath(rangeReview.Country, rangeReview.Title);
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            if (Directory.Exists(sourceDirectoryPath))
            {
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                importerResults = ImporterHelper.ExcelImporterStep1Validations(articlesCorrectHeader, "A2:BR2", files);
            }
            else
            {
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                    ErrorType = ErrorType.Error
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleMasterStep2()
        {
            DateTime start = DateTime.Now;

            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            ExcelRangeBase excelCell = null;
            int? rowNumber = 0;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempArticles]
                TempArticlesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetArticleMasterFilePath(rangeReview.Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempArticle>();

                var categories = CategoriesDomain.GetAll();

                foreach (string file in files.Where(x => x.EndsWith("xlsx")))
                {
                    FileInfo fi = new FileInfo(file);
                    fileName = fi.Name;
                    ExcelPackage excel = new ExcelPackage(fi);
                    DataRow row = null;

                    var segmentations = AttributesDomain.GetAllTypesWithValues();
                    string correctHeaderWithSegmentation = articlesCorrectHeader;

                    var locations = SpacesDomain.GetAllPlanogramLocations();


                    foreach (var sheet in excel.Workbook.Worksheets)
                    {
                        string header = string.Empty;

                        int lastCol = sheet.Dimension.End.Column;
                        int lastRow = sheet.Dimension.End.Row;
                        int fixedColumns = 71;
                        foreach (var cell in sheet.Cells[2, 1, 2, fixedColumns - 1]) //Leo las columnas fijas
                        {
                            if (cell.Value != null)
                            {
                                header += "|" + cell.Value.ToString().Trim().ToLower();
                            }
                        }

                        bool hasSegmentations;
                        int fixedColumnsAndLocations = fixedColumns + locations.Count;
                        try
                        {
                            var cell = sheet.Cells[2, fixedColumnsAndLocations]; //Intento leer segmentaciones

                            if (cell != null & cell.Value != null)
                                hasSegmentations = true;
                            else
                                hasSegmentations = false;
                        }
                        catch
                        {
                            hasSegmentations = false;
                        }

                        if (hasSegmentations)
                        {
                            foreach (var location in locations)
                            {
                                correctHeaderWithSegmentation += "|" + location.Name.ToLower();
                            }

                            foreach (var seg in segmentations.OrderBy(x => x.Id))
                            {
                                correctHeaderWithSegmentation += "|" + seg.Name.ToLower();
                            }

                            foreach (var cell in sheet.Cells[2, fixedColumns, 2, lastCol]) //Leo las columnas de segmentacion dinamicas
                            {
                                if (cell.Value != null)
                                {
                                    header += "|" + cell.Value.ToString().Trim().ToLower();
                                }
                            }
                        }

                        if (header == correctHeaderWithSegmentation)
                        {

                            string code = null;
                            string uom = null;
                            string categoryCode = null;
                            //Las celdas en nulo las saltea
                            foreach (var cell in sheet.Cells["A3:" + sheet.Dimension.End.Address]) //Solo proceso la hoja 1 del Excel
                            {
                                excelCell = cell;

                                string column = cell.Address.Substring(0, 1);
                                int colNum;
                                if (!int.TryParse(cell.Address.Substring(1, 1), out colNum))
                                {
                                    column += cell.Address.Substring(1, 1);
                                }


                                switch (column)
                                {
                                    case "A":
                                        {
                                            row = sourceTable.NewRow();
                                            sourceTable.Rows.Add(row);
                                            rowNumber++;
                                            code = cell.Value.ToString();
                                            row["Code"] = cell.Value.ToString();
                                            row["RangeReviewId"] = rangeReview.Id;
                                            break;
                                        }
                                    case "B":
                                        {
                                            if (cell.Value != null)
                                                row["EAN"] = cell.Value.ToString().PadLeft(13, '0');
                                            else
                                                row["EAN"] = DBNull.Value;
                                            break;
                                        }
                                    case "C":
                                        {
                                            categoryCode = cell.Value.ToString().PadLeft(6, '0');
                                            row["NotFoundCategory"] = cell.Value.ToString().PadLeft(6, '0');
                                            break;
                                        }
                                    case "D":
                                        {
                                            row["Name"] = cell.Value;
                                            break;
                                        }
                                    case "E":
                                        {
                                            row["Manufacturer"] = cell.Value;
                                            break;
                                        }
                                    case "F":
                                        {
                                            row["Price"] = cell.Value ?? DBNull.Value;
                                            break;
                                        }
                                    case "G":
                                        {
                                            uom = cell.Value != null ? cell.Value.ToString() : null;
                                            row["UOM"] = cell.Value;
                                            break;
                                        }
                                    case "H":
                                        {
                                            row["Type"] = cell.Value;
                                            break;
                                        }
                                    case "J":
                                        {
                                            row["OrderUOM"] = cell.Value;
                                            break;
                                        }
                                    case "K":
                                        {
                                            if (cell.Value != null && !string.IsNullOrWhiteSpace(cell.Value.ToString()))
                                            {
                                                row["OrderEAN"] = cell.Value.ToString().PadLeft(13, '0');
                                            }
                                            else
                                            {
                                                row["OrderEAN"] = DBNull.Value;
                                            }
                                            break;
                                        }
                                    case "N":
                                        {
                                            row["Pack"] = cell.Value;
                                            break;
                                        }
                                    case "P":
                                        {
                                            row["Vendor"] = cell.Value;
                                            break;
                                        }
                                    case "R":
                                        {
                                            row["Cost"] = cell.Value ?? DBNull.Value;
                                            break;
                                        }
                                    case "W":
                                        {
                                            row["NetPrice"] = cell.Value ?? DBNull.Value;
                                            break;
                                        }
                                    case "X":
                                        {
                                            row["TaxRate"] = cell.Value == null || string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0m : Convert.ToDecimal(cell.Value) / 100m;
                                            break;
                                        }
                                    case "AJ":
                                        {
                                            row["Brand"] = cell.Value;
                                            break;
                                        }
                                    case "AK":
                                        {
                                            row["CreatedDate"] = cell.Value ?? DBNull.Value;
                                            break;
                                        }
                                    case "AL":
                                        {
                                            row["Size"] = cell.Value ?? DBNull.Value;
                                            break;
                                        }
                                    case "AM":
                                        {
                                            row["Attribute1"] = cell.Value;
                                            break;
                                        }
                                    case "AN":
                                        {
                                            row["Attribute2"] = cell.Value;
                                            break;
                                        }
                                    case "AO":
                                        {
                                            row["Attribute3"] = cell.Value;
                                            break;
                                        }
                                    case "AP":
                                        {
                                            row["Attribute4"] = cell.Value;
                                            break;
                                        }
                                    case "AQ":
                                        {
                                            row["Attribute5"] = cell.Value;
                                            break;
                                        }

                                    //Columnas dinamicas.
                                    default:
                                        {
                                            if (cell.Start.Column >= fixedColumnsAndLocations)
                                            {
                                                if (hasSegmentations)
                                                {
                                                    var colHeader = sheet.Cells[column + "2"].Value;

                                                    if (colHeader != null)
                                                    {
                                                        AttributeType actualSegmentation = segmentations.SingleOrDefault(x => x.Name == colHeader.ToString());

                                                        if (actualSegmentation != null)
                                                        {
                                                            if (cell.Value != null)
                                                            {
                                                                string cellValue = cell.Value.ToString().Trim();
                                                                Entities.Attribute value = null;
                                                                Category cat = ImporterHelper.GetCategoryByCodeCheckingMatches(categoryCode, rangeReview.CountryId);

                                                                if (cat != null)
                                                                {
                                                                    //Valor de Subcategoria
                                                                    value = actualSegmentation.Values.SingleOrDefault(x => x.AttributeValue.Trim() == cellValue && x.CategoryId == cat.Id);

                                                                    if (value == null)
                                                                    {
                                                                        //Valor de Categoria Madre
                                                                        value = actualSegmentation.Values.SingleOrDefault(x => x.AttributeValue.Trim() == cellValue && x.CategoryId == cat.ParentId);

                                                                        if (value == null)
                                                                        {
                                                                            //Valor General del Segmento.
                                                                            value = actualSegmentation.Values.SingleOrDefault(x => x.AttributeValue.Trim() == cellValue);
                                                                        }
                                                                    }

                                                                }


                                                                if (value != null) //El valor de la segmentacion existe en el maestro
                                                                {
                                                                    if (value.CategoryId.HasValue) //Valido si son de categoria, que sean correctos.
                                                                    {

                                                                        if (cat != null)
                                                                        {
                                                                            var validValues = actualSegmentation.Values.Where(x => !x.CategoryId.HasValue || (x.CategoryId.Value == cat.Id || x.CategoryId.Value == cat.ParentId));

                                                                            if (validValues.Select(x => x.AttributeValue.Trim()).Contains(value.AttributeValue.Trim()))
                                                                            {
                                                                                TempArticleAttributesRepository.Insert(new TempArticleAttributeRow { Code = code, UOM = uom, AttributeId = value.Id });
                                                                            }
                                                                            else
                                                                            {
                                                                                importerResults.Add(new ImporterResult
                                                                                {
                                                                                    Category = ResultCategory.TemporalTable,
                                                                                    Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, excelCell.Worksheet.Name, excelCell.Address),
                                                                                    Description = string.Format("El valor de segmentación no es valido. Los valores validos {0} para el articulo {1} son: ({2}).", actualSegmentation.Name, code + " - " + uom, string.Join(", ", validValues.Select(x => x.AttributeValue))),
                                                                                    ErrorType = ErrorType.Error,
                                                                                    Workaround = Workaround.Segmentation,
                                                                                    Id = errorCount
                                                                                });
                                                                            }
                                                                        }
                                                                        else
                                                                        {
                                                                            importerResults.Add(new ImporterResult
                                                                            {
                                                                                Category = ResultCategory.TemporalTable,
                                                                                Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, excelCell.Worksheet.Name, excelCell.Address),
                                                                                Description = string.Format("No puede validarse la segmentación, porque no existe la categoria del artículo."),
                                                                                ErrorType = ErrorType.Error,
                                                                                Workaround = Workaround.Segmentation,
                                                                                Id = errorCount
                                                                            });
                                                                        }

                                                                    }
                                                                    else
                                                                    {
                                                                        TempArticleAttributesRepository.Insert(new TempArticleAttributeRow { Code = code, UOM = uom, AttributeId = value.Id });
                                                                    }
                                                                }
                                                                else
                                                                {
                                                                    importerResults.Add(new ImporterResult
                                                                    {
                                                                        Category = ResultCategory.TemporalTable,
                                                                        Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, excelCell.Worksheet.Name, excelCell.Address),
                                                                        Description = string.Format("El valor de segmentación no es valido. Los valores cargados para {0} son: ({1}).", actualSegmentation.Name, string.Join(", ", actualSegmentation.Values.Select(x => x.AttributeValue))),
                                                                        ErrorType = ErrorType.Error,
                                                                        Workaround = Workaround.Segmentation,
                                                                        Id = errorCount
                                                                    });
                                                                }
                                                            }

                                                        }
                                                        else
                                                        {
                                                            //LG: TODO: ver que pasa si entro aca



                                                            //string salesColumns = "AR,AS,AT,AU,AV,AW,AX,AY,AZ,BA,BB,BC,BD,BE,BF,BG,BH,BI,BJ,BK,BL,BM";

                                                            ////Si no es una columna de ventas, asumo que tiene que ser una de segmentacion.
                                                            //if (!salesColumns.Split(',').Contains(column))
                                                            //{
                                                            //    importerResults.Add(new ImporterResult
                                                            //                                                {
                                                            //                                                    Category = ResultCategory.TemporalTable,
                                                            //                                                    Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, excelCell.Worksheet.Name, excelCell.Address),
                                                            //                                                    Description = string.Format("La columna {0} no es una segmentación valida. La segmentación esperada es: {1}.", colHeader, string.Join(", ", segmentations.Select(x => x.Name))),
                                                            //                                                    ErrorType = ErrorType.Error,
                                                            //                                                    Workaround = Workaround.Segmentation,
                                                            //                                                    Id = errorCount
                                                            //                                                });
                                                            //}
                                                        }

                                                    }
                                                }
                                            }

                                            break;
                                        }
                                }
                            }
                        }
                        else
                        {
                            importerResults.Add(new ImporterResult
                            {
                                ErrorType = ErrorType.Error,
                                Category = ResultCategory.TemporalTable,
                                Description = "La cabecera no coincide con los campos esperados. El formato correcto es: " + correctHeaderWithSegmentation
                            });
                        }
                    }


                    DateTime end = DateTime.Now;
                    Debug.WriteLine("TIEMPO TOTAL: " + (end - start).TotalSeconds);

                    // new method: SQLBulkCopy:
                    TempArticlesRepository.BulkCopy(sourceTable);

                    TempArticlesRepository.UpdateCategoryIds();

                }

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.TemporalTable,
                    Description = Messages.ImportacionATablaTemporalCorrecta
                });
            }
            catch (Exception ex)
            {
                errorCount++;

                string sheetName = excelCell != null ? excelCell.Worksheet.Name : string.Empty;
                string cellAddress = excelCell != null ? excelCell.Address : string.Empty;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, sheetName, cellAddress),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }


        //Ventas XLSX
        public IList<ImporterResult> ImportArticleSalesExcelStep1()
        {
            string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(rangeReview.Country, rangeReview.Title);
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            if (Directory.Exists(sourceDirectoryPath))
            {
                string[] files = Directory.GetFiles(sourceDirectoryPath);
                importerResults = ImporterHelper.ExcelImporterStep1Validations(salesCorrectHeader, "A2:AE2", files);
            }
            else
            {
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                    ErrorType = ErrorType.Error
                });
            }

            return importerResults;

        }

        public IList<ImporterResult> ImportArticleSalesExcelStep2()
        {
            DateTime start = DateTime.Now;

            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            ExcelRangeBase excelCell = null;
            int? rowNumber = 2; //Los datos empiezan en la fila 3.
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempArticleSales]
                TempArticleSalesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(rangeReview.Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempArticleSaleRow>();
                DataTable Sales = ImporterHelper.CreateDataTableFromGateway<TempArticleSaleRow>();
                //List<TempArticleSaleRow> Sales = new List<TempArticleSaleRow>();


                #region Importo el Excel
                foreach (string file in files.Where(x => x.EndsWith("xlsx")))
                {
                    FileInfo fi = new FileInfo(file);
                    fileName = fi.Name;
                    ExcelPackage excel = new ExcelPackage(fi);
                    TempArticleSaleRow sale = null;

                    foreach (var sheet in excel.Workbook.Worksheets)
                    {
                        string header = string.Empty;
                        foreach (var cell in sheet.Cells["A2:AE2"]) //Chequeo la primer hoja del excel
                        {
                            if (cell.Value != null)
                            {
                                header += "|" + cell.Value.ToString().Trim().ToLower();
                            }
                        }

                        if (header == salesCorrectHeader)
                        {
                            int excelRow = 0;
                            DataRow row = null;
                            //Las celdas en nulo las saltea
                            foreach (var cell in sheet.Cells["A3:AE" + sheet.Dimension.End.Row]) //Solo proceso la hoja 1 del Excel
                            {
                                if (ExcelHelper.SplitAddress(cell.Address).FromRow == excelRow)
                                {
                                    continue;
                                }

                                string column = cell.Address.Substring(0, 1);
                                int colNum;
                                if (!int.TryParse(cell.Address.Substring(1, 1), out colNum))
                                {
                                    column += cell.Address.Substring(1, 1);
                                }

                                //Si la columna no es la A o si la columna es la A pero está en blanco
                                if (excelCell != null && excelCell.Start.Row != cell.Start.Row)
                                {
                                    if (column != "A")
                                    {
                                        //processRow = false;
                                        excelRow = ExcelHelper.SplitAddress(cell.Address).FromRow;
                                        continue;
                                    }
                                    else
                                    {
                                        if (cell.Value == null || string.IsNullOrWhiteSpace(cell.Value.ToString()))
                                        {
                                            //processRow = false;
                                            excelRow = ExcelHelper.SplitAddress(cell.Address).FromRow;
                                            continue;
                                        }
                                    }
                                }

                                excelCell = cell;

                                switch (column)
                                {
                                    case "A":
                                        {
                                            row = Sales.NewRow();
                                            Sales.Rows.Add(row);
                                            row["BonusBuyNumber"] = cell.Value.ToString();
                                            rowNumber++;
                                            break;
                                        }
                                    case "B":
                                        {
                                            row["StoreCode"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "C":
                                        {
                                            row["StoreName"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "D":
                                        {
                                            row["ArticleCode"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "E":
                                        {
                                            row["ArticleName"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "F":
                                        {
                                            row["Date"] = DateTime.Parse(cell.Value.ToString());
                                            break;
                                        }
                                    case "G":
                                        {
                                            row["EAN"] = cell.Value.ToString().PadLeft(13, '0');
                                            break;
                                        }
                                    case "H":
                                        {
                                            row["TaxRate"] = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 1 : decimal.Parse(cell.Value.ToString());
                                            break;
                                        }
                                    case "I":
                                        {
                                            row["Size"] = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : decimal.Parse(cell.Value.ToString());
                                            break;
                                        }
                                    case "J":
                                        {
                                            row["Attribute1"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "K":
                                        {
                                            row["Attribute2"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "L":
                                        {
                                            row["Attribute3"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "M":
                                        {
                                            row["Attribute4"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "N":
                                        {
                                            row["Attribute5"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "O":
                                        {
                                            row["Manufacturer"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "P":
                                        {
                                            row["Brand"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "R":
                                        {
                                            row["NotFoundCategory"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "T":
                                        {
                                            row["UOM"] = cell.Value.ToString();
                                            break;
                                        }
                                    case "Y":
                                        {
                                            row["Units"] = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : Convert.ToInt32(decimal.Parse(cell.Value.ToString()));
                                            break;
                                        }
                                    case "Z":
                                        {
                                            row["Sales"] = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0m : decimal.Parse(cell.Value.ToString());
                                            break;
                                        }
                                    case "AA":
                                        {
                                            row["GM"] = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0m : decimal.Parse(cell.Value.ToString());
                                            break;
                                        }
                                    case "AB":
                                        {
                                            row["GrossSales"] = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0m : decimal.Parse(cell.Value.ToString());
                                            break;
                                        }
                                    case "AC":
                                        {
                                            row["NetMargin"] = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0m : decimal.Parse(cell.Value.ToString());
                                            break;
                                        }
                                    case "AD":
                                        {
                                            row["Costs"] = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0m : decimal.Parse(cell.Value.ToString());
                                            break;
                                        }
                                    case "AE":
                                        {
                                            row["CostsWithVat"] = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0m : decimal.Parse(cell.Value.ToString());
                                            break;
                                        }
                                }
                            }
                        }
                    }

                }
                #endregion

                #region ChequeoVentaDiaria

                //if (rangeReview.Country.DailySales)
                //{
                //    //Agrupo por dia por tienda
                //    var groupedByDay = from s in Sales
                //                       group s by new { s.StoreCode, s.Date.Year, s.Date.Month, s.Date.Day } into g
                //                       select new
                //                       {
                //                           Year = g.Key.Year,
                //                           Month = g.Key.Month,
                //                           Day = g.Key.Day,
                //                           StoreCode = g.Key.StoreCode
                //                       };

                //    DateTime startDate = rangeReview.HasPriorYear ? rangeReview.FirstMonth.AddMonths(-12) : rangeReview.FirstMonth;
                //    int months = rangeReview.HasPriorYear ? rangeReview.TotalMonths + 12 : rangeReview.TotalMonths;
                //    List<string> stores = (from s in groupedByDay group s by s.StoreCode into g select g.Key).ToList();
                //    List<DateTime> dates = new List<DateTime>();
                //    for (int i = 0; i < months; i++)
                //    {
                //        DateTime date = startDate.AddMonths(i);
                //        //Loopeo por los dias de cada mes
                //        for (int m = 0; m < date.AddMonths(1).AddDays(-1).Day; m++)
                //        {
                //            dates.Add(date.AddDays(m));
                //        }
                //    }

                //    var emptyTableQuery = from dt in dates
                //                          from s in stores
                //                          select new
                //                          {
                //                              Year = dt.Year,
                //                              Month = dt.Month,
                //                              Day = dt.Day,
                //                              StoreCode = s
                //                          };

                //    var finalQuery = from e in emptyTableQuery
                //                     join g in groupedByDay on new { e.Year, e.Month, e.Day, e.StoreCode } equals new { g.Year, g.Month, g.Day, g.StoreCode } into dtg
                //                     from j in dtg.DefaultIfEmpty()
                //                     select new { Year = e.Year, Month = e.Month, Day = e.Day, StoreCode = e.StoreCode, Missing = j == null ? true : false };

                //    List<TempArticleSaleRow> articlesAverageMonthlySales = (from s in Sales
                //                                                            group s by new
                //                                                            {
                //                                                                s.CountryId,
                //                                                                s.StoreId,
                //                                                                s.StoreCode,
                //                                                                s.StoreName,
                //                                                                s.Date.Year,
                //                                                                s.Date.Month,
                //                                                                s.ArticleId,
                //                                                                s.ArticleCode,
                //                                                                s.ArticleName,
                //                                                                s.EAN,
                //                                                                s.UOM,
                //                                                                s.CategoryId,
                //                                                                s.NotFoundCategory,
                //                                                                s.TaxRate
                //                                                            } into g
                //                                                            select new TempArticleSaleRow
                //                                                            {
                //                                                                CountryId = g.Key.CountryId,
                //                                                                StoreId = g.Key.StoreId,
                //                                                                StoreCode = g.Key.StoreCode,
                //                                                                StoreName = g.Key.StoreName,
                //                                                                Date = new DateTime(g.Key.Year, g.Key.Month, 1),
                //                                                                ArticleId = g.Key.ArticleId,
                //                                                                ArticleCode = g.Key.ArticleCode,
                //                                                                ArticleName = g.Key.ArticleName,
                //                                                                EAN = g.Key.EAN,
                //                                                                UOM = g.Key.UOM,
                //                                                                CategoryId = g.Key.CategoryId,
                //                                                                NotFoundCategory = g.Key.NotFoundCategory,
                //                                                                Units = Convert.ToInt32(g.Average(x => x.Units)),
                //                                                                Sales = g.Average(x => x.Sales),
                //                                                                GrossSales = g.Average(x => x.GrossSales),
                //                                                                GM = g.Average(x => x.GM),
                //                                                                TaxRate = g.Key.TaxRate
                //                                                            }).ToList();

                //    int days = 0;
                //    int storesCount = finalQuery.Where(x => x.Missing).Select(x => x.StoreCode).Distinct().Count();
                //    //Si no hay registros para un dia determinado agrego un error
                //    foreach (var m in finalQuery.Where(x => x.Missing))
                //    {
                //        days++;
                //        errorCount++;
                //        importerResults.Add(new ImporterResult
                //        {
                //            //INFO: Lila los prados y venezuela tienen dias faltantes.
                //            Category = ResultCategory.TemporalTable,
                //            Description = string.Format("No hay ventas el día {0} para la tienda {1}", new DateTime(m.Year, m.Month, m.Day).ToShortDateString(), m.StoreCode),
                //            ErrorType = ErrorType.Warning,
                //            Param1 = new DateTime(m.Year, m.Month, m.Day).ToString("mm-MMMM"),
                //            Param2 = m.StoreCode,
                //            Id = errorCount
                //        });

                //        //TODO: insertar venta promedio para ese dia para cada producto?
                //        //DB: Posible solución: Agregar estas ventas faltantes con una marca, por ej. IsMissingSales = true.
                //        //    y en el paso 3 de la importación que el analista tome la decición de que hacer con esos faltantes. 
                //        Sales.AddRange(articlesAverageMonthlySales.Where(x => x.Date.Year == m.Year && x.Date.Month == m.Month && x.StoreCode == m.StoreCode));
                //    }

                //    if (days > 0)
                //    {
                //        importerResults.Add(new ImporterResult
                //        {
                //            Category = ResultCategory.TemporalTable,
                //            Description = string.Format("Se encontraron {0} días de ventas faltantes para {1} tiendas en total. Solicite nuevamente la informacion para estas tiendas en los meses informados. Si continua se completaran los días faltantes de ventas con el promedio de venta diario del mes correspondiente.", days, storesCount),
                //            ErrorType = ErrorType.Information,
                //            Id = errorCount
                //        });
                //    }
                //}

                #endregion

                #region Sumarizo y Armo DataTable

                //Sumarizo
                var q = from s in Sales.AsEnumerable()
                        //where s.Field<int?>("Units") > 0 && s.Field<decimal?>("Sales") > 0
                        //select s;
                        //from s in Sales.AsEnumerable().Where(x => x.Field<decimal>("Units") > 0 && x.Field<decimal>("Sales") > 0)
                        group s by new
                        {
                            BonusBuyNumber = s.Field<string>("BonusBuyNumber"),
                            StoreCode = s.Field<string>("StoreCode"),
                            StoreName = s.Field<string>("StoreName"),
                            Region = s.Field<string>("Region"),
                            NotFoundCategory = s.Field<string>("NotFoundCategory"),
                            ArticleCode = s.Field<string>("ArticleCode"),
                            ArticleName = s.Field<string>("ArticleName"),
                            EAN = s.Field<string>("EAN"),
                            UOM = s.Field<string>("UOM"),
                            Size = s.Field<decimal?>("Size"),
                            Attribute1 = s.Field<string>("Attribute1"),
                            Attribute2 = s.Field<string>("Attribute2"),
                            Attribute3 = s.Field<string>("Attribute3"),
                            Attribute4 = s.Field<string>("Attribute4"),
                            Attribute5 = s.Field<string>("Attribute5"),
                            Brand = s.Field<string>("Brand"),
                            Manufacturer = s.Field<string>("Manufacturer"),
                            TaxRate = s.Field<decimal?>("TaxRate"),
                            Year = s.Field<DateTime>("Date").Year,
                            Month = s.Field<DateTime>("Date").Month

                        } into g
                        select new
                        {
                            //Articulos
                            RangeReviewId = rangeReview.Id,
                            ArticleCode = g.Key.ArticleCode,
                            ArticleName = g.Key.ArticleName,
                            NotFoundCategory = g.Key.NotFoundCategory,
                            EAN = g.Key.EAN,
                            UOM = g.Key.UOM,
                            Size = g.Key.Size,
                            Attribute1 = g.Key.Attribute1,
                            Attribute2 = g.Key.Attribute2,
                            Attribute3 = g.Key.Attribute3,
                            Attribute4 = g.Key.Attribute4,
                            Attribute5 = g.Key.Attribute5,
                            Brand = g.Key.Brand,
                            Manufacturer = g.Key.Manufacturer,
                            TaxRate = g.Key.TaxRate,

                            //Tiendas
                            CountryId = rangeReview.Country.Id,
                            StoreCode = g.Key.StoreCode,
                            StoreName = g.Key.StoreName,
                            Region = g.Key.Region,

                            //Ventas
                            BonusBuyNumber = g.Key.BonusBuyNumber,
                            Date = new DateTime(g.Key.Year, g.Key.Month, 1),
                            Units = g.Sum(x => x.Field<int?>("Units")),
                            Sales = g.Sum(x => x.Field<decimal?>("Sales")),
                            GM = g.Sum(x => x.Field<decimal?>("GM")),
                            GrossSales = g.Sum(x => x.Field<decimal?>("GrossSales")),
                            NetMargin = g.Sum(x => x.Field<decimal?>("NetMargin")),
                            Costs = g.Sum(x => x.Field<decimal?>("Costs")),
                            CostsWithVat = g.Sum(x => x.Field<decimal?>("CostsWithVat"))
                        };


                //Construyo el DataTable en base a los datos sumarizados
                foreach (var sale in q)
                {
                    DataRow row = sourceTable.NewRow();

                    //Ventas
                    row["BonusBuyNumber"] = sale.BonusBuyNumber;
                    row["Date"] = sale.Date;
                    row["Units"] = sale.Units;
                    row["Sales"] = sale.Sales;
                    row["GM"] = sale.GM;
                    row["GrossSales"] = sale.GrossSales;
                    if (sale.Units > 0)
                    {
                        row["Price"] = sale.GrossSales / sale.Units;
                    }

                    if (sale.NetMargin.HasValue)
                        row["NetMargin"] = sale.NetMargin.Value;
                    else
                        row["NetMargin"] = DBNull.Value;

                    if (sale.Costs.HasValue)
                        row["Costs"] = sale.Costs;
                    else
                        row["Costs"] = DBNull.Value;

                    if (sale.CostsWithVat.HasValue)
                        row["CostsWithVat"] = sale.CostsWithVat;
                    else
                        row["CostsWithVat"] = DBNull.Value;

                    //Articulos
                    row["RangeReviewId"] = sale.RangeReviewId;
                    row["NotFoundCategory"] = sale.NotFoundCategory;
                    row["ArticleCode"] = sale.ArticleCode;
                    row["ArticleName"] = sale.ArticleName;
                    row["EAN"] = sale.EAN;
                    row["UOM"] = sale.UOM;
                    row["Brand"] = sale.Brand;
                    row["Manufacturer"] = sale.Manufacturer;
                    if (sale.TaxRate.HasValue)
                        row["TaxRate"] = sale.TaxRate;
                    else
                        row["TaxRate"] = 1;

                    //Tiendas
                    row["CountryId"] = sale.CountryId;
                    row["StoreCode"] = sale.StoreCode;
                    row["StoreName"] = sale.StoreName;
                    row["Region"] = sale.Region;


                    sourceTable.Rows.Add(row);
                }
                #endregion

                DateTime end = DateTime.Now;
                Debug.WriteLine("TIEMPO TOTAL: " + (end - start).TotalSeconds);

                TempArticleSalesRepository.BulkCopy(sourceTable);

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.TemporalTable,
                    Description = Messages.ImportacionATablaTemporalCorrecta
                });


                //Borro registros con Units en 0 y Sales en 0. TODO: ver si esto es lo mejor. Por ahora lo hacemos asi
                //TempArticleSalesRepository.DeleteZeroSales();

                //Copio todos a la tabla temporal de articulos.
                TempArticlesRepository.TruncateTable();
                TempArticlesRepository.InsertDataFromTempSales();
                TempArticlesRepository.UpdateCategoryIds();

                //Copio a Articles (como delisted) solo los que aun no existian en el maestro. OJO, solo se copian los que se logró actualizar el categoryId.
                int totalRowsInserted = ArticlesRepository.InsertFromTempArticlesAsDelisted(rangeReview.Id);
                if (totalRowsInserted > 0)
                {
                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.TemporalTable,
                        Description = string.Format("Se han agregado al Maestro de Artículos {0} productos hallados en las ventas. Estos productos fueron agregados como De-listados.", totalRowsInserted)
                    });
                }


                TempArticleSalesRepository.UpdateArticleIds(rangeReview.Id);
                TempArticleSalesRepository.UpdateStoreIds();

            }
            catch (Exception ex)
            {
                errorCount++;

                string sheetName = excelCell != null ? excelCell.Worksheet.Name : string.Empty;
                string cellAddress = excelCell != null ? excelCell.Address : string.Empty;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, sheetName, cellAddress),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }


        //Ventas CSV
        public IList<ImporterResult> ImportArticleSalesStep1()
        {
            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                //Check Prerequisites
                //1. Chequeo que estén los archivos
                string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(rangeReview.Country, rangeReview.Title);

                if (Directory.Exists(sourceDirectoryPath))
                {
                    string[] files = Directory.GetFiles(sourceDirectoryPath, "*.csv");

                    if (files.Length == 0)
                    {
                        errorCount++;
                        importerResults.Add(new ImporterResult
                        {
                            Category = ResultCategory.PreRequisites,
                            Description = Errors.NoFilesToImport,
                            ErrorType = ErrorType.Error,
                            Id = errorCount
                        });
                    }
                    else
                    {
                        importerResults.Add(new ImporterResult
                        {
                            ErrorType = ErrorType.Information,
                            Category = ResultCategory.PreRequisites,
                            Description = Messages.ChequeoArchivosCorrecto
                        });
                    }

                    //SALES
                    foreach (string file in files.Where(x => x.EndsWith("csv")))
                    {
                        fileName = file;

                        string[] fields;
                        rowNumber = 1;
                        using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                        {
                            line = sr.ReadLine();
                            line = sr.ReadLine();

                            if (!line.ToLower().Equals(salesCorrectHeaderCSV))
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = string.Format("{0} el formato correcto es: {1}", Errors.ImporterInvalidHeaderFormat, salesCorrectHeaderCSV),
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }
                            else
                            {
                                importerResults.Add(new ImporterResult
                                {
                                    ErrorType = ErrorType.Information,
                                    Category = ResultCategory.PreRequisites,
                                    Description = string.Format(Messages.ChequeoCabeceraCorrecto, fileName)
                                });
                            }

                            line = sr.ReadLine();
                            rowNumber++;
                            fields = line.Split(';');
                            if (fields.Count() != 31)
                            {
                                errorCount++;
                                importerResults.Add(new ImporterResult
                                {
                                    Category = ResultCategory.PreRequisites,
                                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                    Description = Errors.ImporterErrorInvalidNumberOfFields,
                                    ErrorType = ErrorType.Error,
                                    Id = errorCount
                                });
                            }

                        }
                    }
                }
                else
                {
                    importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.PreRequisites,
                        Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                        ErrorType = ErrorType.Error
                    });
                }

            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.PreRequisites,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

        public IList<ImporterResult> ImportArticleSalesStep2()
        {
            //LG: Meter culture como parámetro del Country 
            //Todos los CSVs tienen que estar con , como separador decimal y dd/mm/yyyy como formato de fechas
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("es-AR");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            int? rowNumber = null;
            string line = null;
            int errorCount = 0;

            try
            {
                // Limpio las tablas temporales [TempArticleSales].
                TempArticleSalesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(rangeReview.Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath, "*.csv");

                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempArticleSaleRow>();
                
                //Sales
                foreach (string file in files)
                {
                    fileName = file;
                    rowNumber = 0;

                    string[] fields;
                    DataRow row = null;

                    #region CSV Parser

                    using (StreamReader sr = new StreamReader(file, System.Text.Encoding.Default, true))
                    {

                        sr.ReadLine(); //Salteo la primer linea.

                        while (!sr.EndOfStream)
                        {
                            line = sr.ReadLine();
                            rowNumber++;
                            if (!line.ToLower().StartsWith("bonus buy number;site;site name;") && !line.StartsWith("-----") && !line.StartsWith(";;;") && !line.StartsWith(",,,") && !string.IsNullOrWhiteSpace(line))
                            {
                                fields = line.Split(';');

                                if (fields != null && fields.Count() == 31)
                                {
                                    try
                                    {
                                        row = sourceTable.NewRow();
                                        

                                        //Article & Store
                                        row["RangeReviewId"] = rangeReview.Id;
                                        row["CountryId"] = rangeReview.CountryId;
                                        row["BonusBuyNumber"] = fields[0];
                                        row["StoreCode"] = fields[1];
                                        row["StoreName"] = fields[2];
                                        row["ArticleCode"] = fields[3];
                                        row["ArticleName"] = fields[4];
                                        row["Date"] = DateTime.Parse(fields[5]);
                                        row["EAN"] = fields[6];
                                        row["TaxRate"] = string.IsNullOrWhiteSpace(fields[7]) ? 1 : decimal.Parse(fields[7]);
                                        row["Size"] = string.IsNullOrWhiteSpace(fields[8]) ? 0 : decimal.Parse(fields[8]);
                                        row["Attribute1"] = fields[9];
                                        row["Attribute2"] = fields[10];
                                        row["Attribute3"] = fields[11];
                                        row["Attribute4"] = fields[12];
                                        row["Attribute5"] = fields[13];
                                        row["Manufacturer"] = fields[14];
                                        row["Brand"] = fields[15];
                                        row["NotFoundCategory"] = fields[17];
                                        row["UOM"] = fields[19];

                                        //Sales
                                        row["Units"] = string.IsNullOrWhiteSpace(fields[24]) ? 0 : Convert.ToInt32(decimal.Parse(fields[24]));
                                        row["Sales"] = string.IsNullOrWhiteSpace(fields[25]) ? 0 : decimal.Parse(fields[25]);
                                        row["GM"] = string.IsNullOrWhiteSpace(fields[26]) ? 0 : decimal.Parse(fields[26]);
                                        row["GrossSales"] = string.IsNullOrWhiteSpace(fields[27]) ? 0 : decimal.Parse(fields[27]);
                                        row["NetMargin"] = string.IsNullOrWhiteSpace(fields[28]) ? 0 : decimal.Parse(fields[28]);
                                        row["Costs"] = string.IsNullOrWhiteSpace(fields[29]) ? 0 : decimal.Parse(fields[29]);
                                        row["CostsWithVat"] = string.IsNullOrWhiteSpace(fields[30]) ? 0 : decimal.Parse(fields[30]);

                                        if (row.Field<int>("Units") > 0 && row.Field<decimal>("Sales") > 0)
                                        {
                                            sourceTable.Rows.Add(row);
                                        }

                                    }
                                    catch (Exception ex)
                                    {
                                        errorCount++;
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = ex.Message,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }
                                else
                                {
                                    errorCount++;

                                    if (fields == null)
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorParsingLine,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                    else
                                    {
                                        importerResults.Add(new ImporterResult
                                        {
                                            Category = ResultCategory.TemporalTable,
                                            Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                                            Description = Errors.ImporterErrorInvalidNumberOfFields,
                                            ErrorType = ErrorType.Error,
                                            Id = errorCount
                                        });
                                    }
                                }
                            }
                        }


                    }

                    #endregion

                    //Copio los datos a la BD por cada Archivo que proceso. 
                    TempArticleSalesRepository.BulkCopy(sourceTable);

                     //Intento liberar la memoria utilizada.
                    sourceTable = null;
                    sourceTable = ImporterHelper.CreateDataTableFromGateway<TempArticleSaleRow>();
                }

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.TemporalTable,
                    Description = Messages.ImportacionATablaTemporalCorrecta
                });


                //Copio todos a la tabla temporal de articulos.
                TempArticlesRepository.TruncateTable();
                TempArticlesRepository.InsertDataFromTempSales();
                TempArticlesRepository.UpdateCategoryIds();


                

                //Copio a Articles (como delisted) solo los que aun no existian en el maestro. OJO, solo se copian los que se logró actualizar el categoryId.
                int totalRowsInserted = ArticlesRepository.InsertFromTempArticlesAsDelisted(rangeReview.Id);
                if (totalRowsInserted > 0)
                {
                    importerResults.Add(new ImporterResult
                    {
                        ErrorType = ErrorType.Information,
                        Category = ResultCategory.TemporalTable,
                        Description = string.Format("Se han agregado al Maestro de Artículos {0} productos hallados en las ventas. Estos productos fueron agregados como De-listados.", totalRowsInserted)
                    });
                }


                TempArticleSalesRepository.UpdateArticleIds(rangeReview.Id);
                TempArticleSalesRepository.UpdateStoreIds();

            }
            catch (Exception ex)
            {
                errorCount++;
                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }


        //Importador Unificado
        public IList<ImporterResult> UnifiedImporterStep1()
        {
            string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(rangeReview.Country, rangeReview.Title);

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            if (Directory.Exists(sourceDirectoryPath))
            {
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                importerResults = ImporterHelper.ExcelImporterStep1Validations(salesCorrectHeader, "A2:AE2", files);
            }
            else
            {
                importerResults.Add(new ImporterResult
                    {
                        Category = ResultCategory.PreRequisites,
                        Description = string.Format("No se ha encontrado el directorio '{0}'", sourceDirectoryPath),
                        ErrorType = ErrorType.Error
                    });
            }

            return importerResults;

            //IList<ImporterResult> importerResults = new List<ImporterResult>();
            //string fileName = null;
            //int? rowNumber = null;
            //int errorCount = 0;
            //try
            //{
            //    //Check Prerequisites
            //    //1. Chequeo que esté el archivo
            //    string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(rangeReview.Country, rangeReview.Title);
            //    string[] files = Directory.GetFiles(sourceDirectoryPath);
            //    if (files.Length == 0)
            //    {
            //        errorCount++;
            //        importerResults.Add(new ImporterResult
            //        {
            //            Category = ResultCategory.PreRequisites,
            //            Description = Errors.NoFilesToImport,
            //            ErrorType = ErrorType.Error,
            //            Id = errorCount
            //        });
            //    }
            //    else
            //    {

            //        importerResults.Add(new ImporterResult
            //        {
            //            ErrorType = ErrorType.Information,
            //            Category = ResultCategory.PreRequisites,
            //            Description = Messages.ChequeoArchivosCorrecto
            //        });

            //        //2. Chequeo que los archivos sean xls o xlsx
            //        if (!files.Any(x => x.EndsWith("xlsx")))
            //        {
            //            errorCount++;
            //            importerResults.Add(new ImporterResult
            //            {
            //                Category = ResultCategory.PreRequisites,
            //                Description = Errors.ImporterErrorExcel2007Format,
            //                ErrorType = ErrorType.Error,
            //                Id = errorCount
            //            });
            //        }
            //        else
            //        {
            //            foreach (string file in files.Where(x => x.EndsWith("xlsx")))
            //            {
            //                ExcelPackage excel = new ExcelPackage(new FileInfo(file));
            //                if (excel != null && excel.Workbook != null)
            //                {
            //                    //Chequeo el formato
            //                    bool allSheetsOK = true;

            //                    foreach (var sheet in excel.Workbook.Worksheets)
            //                    {
            //                        string header = string.Empty;

            //                        foreach (var cell in sheet.Cells["A2:AE2"]) //Chequeo la primer hoja del excel
            //                        {
            //                            if (cell.Value != null)
            //                            {
            //                                header += "|" + cell.Value.ToString().Trim().ToLower();
            //                            }
            //                        }

            //                        if (!string.IsNullOrWhiteSpace(header) && header != salesCorrectHeader)
            //                        {
            //                            allSheetsOK = false;
            //                            errorCount++;
            //                            importerResults.Add(new ImporterResult
            //                            {
            //                                Category = ResultCategory.PreRequisites,
            //                                Description = "La planilla de ventas debe comenzar en la primer hoja del Excel en la celda A2 y tener las siguientes columnas: " + salesCorrectHeader,
            //                                Location = string.Format("Archivo: {0} - Hoja: {1}", fileName, sheet.Name),
            //                                ErrorType = ErrorType.Error,
            //                                Id = errorCount
            //                            });
            //                        }
            //                    }

            //                    if (allSheetsOK)
            //                    {
            //                        importerResults.Add(new ImporterResult
            //                        {
            //                            ErrorType = ErrorType.Information,
            //                            Category = ResultCategory.PreRequisites,
            //                            Description = string.Format(Messages.ChequeoCabeceraCorrecto, fileName)
            //                        });
            //                    }
            //                }
            //            }
            //        }

            //    }
            //}
            //catch (Exception ex)
            //{
            //    errorCount++;
            //    importerResults.Add(new ImporterResult
            //    {
            //        Category = ResultCategory.PreRequisites,
            //        Location = ImporterHelper.GetErrorLocation(fileName, rowNumber),
            //        Description = ex.Message,
            //        ErrorType = ErrorType.Error,
            //        Id = errorCount
            //    });
            //}

            //return importerResults;
        }

        public IList<ImporterResult> UnifiedImporterStep2()
        {
            DateTime start = DateTime.Now;

            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-GB");

            IList<ImporterResult> importerResults = new List<ImporterResult>();

            string fileName = null;
            ExcelRangeBase excelCell = null;
            int? rowNumber = 2; //Los datos empiezan en la fila 3.
            int errorCount = 0;


            try
            {
                // Limpio las tablas temporales [TempArticleSales]
                TempArticleSalesRepository.TruncateTable();

                string sourceDirectoryPath = ImporterHelper.GetSalesFilesPath(rangeReview.Country, rangeReview.Title);
                string[] files = Directory.GetFiles(sourceDirectoryPath);

                DataTable sourceTable = ImporterHelper.CreateDataTableFromGateway<TempArticleSale>();
                List<TempArticleSaleRow> Sales = new List<TempArticleSaleRow>();

                #region Importo el Excel

                foreach (string file in files.Where(x => x.EndsWith("xlsx")))
                {
                    FileInfo fi = new FileInfo(file);
                    fileName = fi.Name;
                    ExcelPackage excel = new ExcelPackage(fi);
                    TempArticleSaleRow sale = null;


                    foreach (var sheet in excel.Workbook.Worksheets)
                    {
                        string header = string.Empty;
                        foreach (var cell in sheet.Cells["A2:AE2"]) //Chequeo la primer hoja del excel
                        {
                            if (cell.Value != null)
                            {
                                header += "|" + cell.Value.ToString().Trim().ToLower();
                            }
                        }

                        if (header == salesCorrectHeader)
                        {

                            //Las celdas en nulo las saltea
                            foreach (var cell in sheet.Cells["A3:AE" + sheet.Dimension.End.Row]) //Solo proceso la hoja 1 del Excel
                            {
                                excelCell = cell;

                                if (cell.Value != null)
                                {

                                    string column = cell.Address.Substring(0, 1);
                                    int colNum;
                                    if (!int.TryParse(cell.Address.Substring(1, 1), out colNum))
                                    {
                                        column += cell.Address.Substring(1, 1);
                                    }

                                    switch (column)
                                    {
                                        case "A":
                                            {
                                                sale = new TempArticleSaleRow();
                                                Sales.Add(sale);
                                                rowNumber++;
                                                sale.BonusBuyNumber = cell.Value.ToString();
                                                break;
                                            }
                                        case "B":
                                            {
                                                sale.StoreCode = cell.Value.ToString();
                                                break;
                                            }
                                        case "C":
                                            {
                                                sale.StoreName = cell.Value.ToString();
                                                break;
                                            }
                                        case "D":
                                            {
                                                sale.ArticleCode = cell.Value.ToString();
                                                break;
                                            }
                                        case "E":
                                            {
                                                sale.ArticleName = cell.Value.ToString();
                                                break;
                                            }
                                        case "F":
                                            {
                                                sale.Date = DateTime.Parse(cell.Value.ToString());
                                                break;
                                            }
                                        case "G":
                                            {
                                                sale.EAN = cell.Value.ToString().PadLeft(13, '0');
                                                break;
                                            }
                                        case "H":
                                            {
                                                sale.TaxRate = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : decimal.Parse(cell.Value.ToString());
                                                break;
                                            }
                                        case "I":
                                            {
                                                sale.Size = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : decimal.Parse(cell.Value.ToString());
                                                break;
                                            }
                                        case "J":
                                            {
                                                sale.Attribute1 = cell.Value.ToString();
                                                break;
                                            }
                                        case "K":
                                            {
                                                sale.Attribute2 = cell.Value.ToString();
                                                break;
                                            }
                                        case "L":
                                            {
                                                sale.Attribute3 = cell.Value.ToString();
                                                break;
                                            }
                                        case "M":
                                            {
                                                sale.Attribute4 = cell.Value.ToString();
                                                break;
                                            }
                                        case "N":
                                            {
                                                sale.Attribute5 = cell.Value.ToString();
                                                break;
                                            }
                                        case "O":
                                            {
                                                sale.Manufacturer = cell.Value.ToString();
                                                break;
                                            }
                                        case "P":
                                            {
                                                sale.Brand = cell.Value.ToString();
                                                break;
                                            }
                                        case "R":
                                            {
                                                sale.NotFoundCategory = cell.Value.ToString();
                                                break;
                                            }
                                        case "T":
                                            {
                                                sale.UOM = cell.Value.ToString();
                                                break;
                                            }
                                        case "Y":
                                            {
                                                sale.Units = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : Convert.ToInt32(cell.Value);
                                                break;
                                            }
                                        case "Z":
                                            {
                                                sale.Sales = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : decimal.Parse(cell.Value.ToString());
                                                break;
                                            }
                                        case "AA":
                                            {
                                                sale.GM = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : decimal.Parse(cell.Value.ToString());
                                                break;
                                            }
                                        case "AB":
                                            {
                                                sale.GrossSales = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : decimal.Parse(cell.Value.ToString());
                                                break;
                                            }
                                        case "AC":
                                            {
                                                sale.NetMargin = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : decimal.Parse(cell.Value.ToString());
                                                break;
                                            }
                                        case "AD":
                                            {
                                                sale.Costs = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : decimal.Parse(cell.Value.ToString());
                                                break;
                                            }
                                        case "AE":
                                            {
                                                sale.CostsWithVat = string.IsNullOrWhiteSpace(cell.Value.ToString()) ? 0 : decimal.Parse(cell.Value.ToString());
                                                break;
                                            }
                                    }
                                }
                            }
                        }
                    }

                }
                #endregion

                #region ChequeoVentaDiaria

                if (rangeReview.Country.DailySales)
                {
                    //Agrupo por dia por tienda
                    var groupedByDay = from s in Sales
                                       group s by new { s.StoreCode, s.Date.Year, s.Date.Month, s.Date.Day } into g
                                       select new
                                       {
                                           Year = g.Key.Year,
                                           Month = g.Key.Month,
                                           Day = g.Key.Day,
                                           StoreCode = g.Key.StoreCode
                                       };

                    DateTime startDate = rangeReview.HasPriorYear ? rangeReview.FirstMonth.AddMonths(-12) : rangeReview.FirstMonth;
                    int months = rangeReview.HasPriorYear ? rangeReview.TotalMonths + 12 : rangeReview.TotalMonths;
                    List<string> stores = (from s in groupedByDay group s by s.StoreCode into g select g.Key).ToList();
                    List<DateTime> dates = new List<DateTime>();
                    for (int i = 0; i < months; i++)
                    {
                        DateTime date = startDate.AddMonths(i);
                        //Loopeo por los dias de cada mes
                        for (int m = 0; m < date.AddMonths(1).AddDays(-1).Day; m++)
                        {
                            dates.Add(date.AddDays(m));
                        }
                    }

                    var emptyTableQuery = from dt in dates
                                          from s in stores
                                          select new
                                          {
                                              Year = dt.Year,
                                              Month = dt.Month,
                                              Day = dt.Day,
                                              StoreCode = s
                                          };

                    var finalQuery = from e in emptyTableQuery
                                     join g in groupedByDay on new { e.Year, e.Month, e.Day, e.StoreCode } equals new { g.Year, g.Month, g.Day, g.StoreCode } into dtg
                                     from j in dtg.DefaultIfEmpty()
                                     select new { Year = e.Year, Month = e.Month, Day = e.Day, StoreCode = e.StoreCode, Missing = j == null ? true : false };

                    List<TempArticleSaleRow> articlesAverageMonthlySales = (from s in Sales
                                                                            group s by new
                                                                            {
                                                                                s.CountryId,
                                                                                s.StoreId,
                                                                                s.StoreCode,
                                                                                s.StoreName,
                                                                                s.Date.Year,
                                                                                s.Date.Month,
                                                                                s.ArticleId,
                                                                                s.ArticleCode,
                                                                                s.ArticleName,
                                                                                s.EAN,
                                                                                s.UOM,
                                                                                s.CategoryId,
                                                                                s.NotFoundCategory,
                                                                                s.TaxRate
                                                                            } into g
                                                                            select new TempArticleSaleRow
                                                                            {
                                                                                CountryId = g.Key.CountryId,
                                                                                StoreId = g.Key.StoreId,
                                                                                StoreCode = g.Key.StoreCode,
                                                                                StoreName = g.Key.StoreName,
                                                                                Date = new DateTime(g.Key.Year, g.Key.Month, 1),
                                                                                ArticleId = g.Key.ArticleId,
                                                                                ArticleCode = g.Key.ArticleCode,
                                                                                ArticleName = g.Key.ArticleName,
                                                                                EAN = g.Key.EAN,
                                                                                UOM = g.Key.UOM,
                                                                                CategoryId = g.Key.CategoryId,
                                                                                NotFoundCategory = g.Key.NotFoundCategory,
                                                                                Units = Convert.ToInt32(g.Average(x => x.Units)),
                                                                                Sales = g.Average(x => x.Sales),
                                                                                GrossSales = g.Average(x => x.GrossSales),
                                                                                GM = g.Average(x => x.GM),
                                                                                TaxRate = g.Key.TaxRate
                                                                            }).ToList();

                    int days = 0;
                    int storesCount = finalQuery.Where(x => x.Missing).Select(x => x.StoreCode).Distinct().Count();
                    //Si no hay registros para un dia determinado agrego un error
                    foreach (var m in finalQuery.Where(x => x.Missing))
                    {
                        days++;
                        errorCount++;
                        importerResults.Add(new ImporterResult
                        {
                            //INFO: Lila los prados y venezuela tienen dias faltantes.
                            Category = ResultCategory.TemporalTable,
                            Description = string.Format("No hay ventas el día {0} para la tienda {1}", new DateTime(m.Year, m.Month, m.Day).ToShortDateString(), m.StoreCode),
                            ErrorType = ErrorType.Warning,
                            Param1 = new DateTime(m.Year, m.Month, m.Day).ToString("mm-MMMM"),
                            Param2 = m.StoreCode,
                            Id = errorCount
                        });

                        //TODO: insertar venta promedio para ese dia para cada producto?
                        //DB: Posible solución: Agregar estas ventas faltantes con una marca, por ej. IsMissingSales = true.
                        //    y en el paso 3 de la importación que el analista tome la decición de que hacer con esos faltantes. 
                        Sales.AddRange(articlesAverageMonthlySales.Where(x => x.Date.Year == m.Year && x.Date.Month == m.Month && x.StoreCode == m.StoreCode));
                    }

                    if (days > 0)
                    {
                        importerResults.Add(new ImporterResult
                        {
                            Category = ResultCategory.TemporalTable,
                            Description = string.Format("Se encontraron {0} días de ventas faltantes para {1} tiendas en total. Solicite nuevamente la informacion para estas tiendas en los meses informados. Si continua se completaran los días faltantes de ventas con el promedio de venta diario del mes correspondiente.", days, storesCount),
                            ErrorType = ErrorType.Information,
                            Id = errorCount
                        });
                    }
                }

                #endregion

                #region Sumarizo y Armo DataTable

                //Sumarizo
                var q = from s in Sales
                        group s by new
                        {
                            s.BonusBuyNumber,
                            s.StoreCode,
                            s.StoreName,
                            s.Region,
                            s.NotFoundCategory,
                            s.ArticleCode,
                            s.ArticleName,
                            s.EAN,
                            s.UOM,
                            s.Size,
                            s.Attribute1,
                            s.Attribute2,
                            s.Attribute3,
                            s.Attribute4,
                            s.Attribute5,
                            s.Brand,
                            s.Manufacturer,
                            s.TaxRate,
                            s.Date.Year,
                            s.Date.Month

                        } into g
                        select new
                        {
                            //Articulos
                            RangeReviewId = rangeReview.Id,
                            ArticleCode = g.Key.ArticleCode,
                            ArticleName = g.Key.ArticleName,
                            NotFoundCategory = g.Key.NotFoundCategory,
                            EAN = g.Key.EAN,
                            UOM = g.Key.UOM,
                            Size = g.Key.Size,
                            Attribute1 = g.Key.Attribute1,
                            Attribute2 = g.Key.Attribute2,
                            Attribute3 = g.Key.Attribute3,
                            Attribute4 = g.Key.Attribute4,
                            Attribute5 = g.Key.Attribute5,
                            Brand = g.Key.Brand,
                            Manufacturer = g.Key.Manufacturer,
                            TaxRate = g.Key.TaxRate,

                            //Tiendas
                            CountryId = rangeReview.Country.Id,
                            StoreCode = g.Key.StoreCode,
                            StoreName = g.Key.StoreName,
                            Region = g.Key.Region,

                            //Ventas
                            BonusBuyNumber = g.Key.BonusBuyNumber,
                            Date = new DateTime(g.Key.Year, g.Key.Month, 1),
                            Units = g.Sum(x => x.Units),
                            Sales = g.Sum(x => x.Sales),
                            GM = g.Sum(x => x.GM),
                            GrossSales = g.Sum(x => x.GrossSales),
                            NetMargin = g.Sum(x => x.NetMargin),
                            Costs = g.Sum(x => x.Costs),
                            CostsWithVat = g.Sum(x => x.CostsWithVat)
                        };

                //Construyo el DataTable en base a los datos sumarizados
                foreach (var sale in q)
                {
                    DataRow row = sourceTable.NewRow();

                    //Ventas
                    row["BonusBuyNumber"] = sale.BonusBuyNumber;
                    row["Date"] = sale.Date;
                    row["Units"] = sale.Units;
                    row["Sales"] = sale.Sales;
                    row["GM"] = sale.GM;
                    row["GrossSales"] = sale.GrossSales;
                    if (sale.Units > 0)
                    {
                        row["Price"] = sale.GrossSales / sale.Units;
                    }
                    row["NetMargin"] = sale.NetMargin;
                    row["Costs"] = sale.Costs;
                    row["CostsWithVat"] = sale.CostsWithVat;

                    //Articulos
                    row["RangeReviewId"] = sale.RangeReviewId;
                    row["NotFoundCategory"] = sale.NotFoundCategory;
                    row["ArticleCode"] = sale.ArticleCode;
                    row["ArticleName"] = sale.ArticleName;
                    row["EAN"] = sale.EAN;
                    row["UOM"] = sale.UOM;
                    row["Brand"] = sale.Brand;
                    row["Manufacturer"] = sale.Manufacturer;
                    row["TaxRate"] = sale.TaxRate.HasValue ? sale.TaxRate : 1;

                    //Tiendas
                    row["CountryId"] = sale.CountryId;
                    row["StoreCode"] = sale.StoreCode;
                    row["StoreName"] = sale.StoreName;
                    row["Region"] = sale.Region;


                    sourceTable.Rows.Add(row);
                }
                #endregion

                DateTime end = DateTime.Now;
                Debug.WriteLine("TIEMPO TOTAL: " + (end - start).TotalSeconds);

                //Inserto en tabla de ventas unificada.
                TempArticleSalesRepository.BulkCopy(sourceTable);


                //Copio a la tabla temporal de articulos.
                TempArticlesRepository.TruncateTable();
                TempArticlesRepository.InsertDataFromTempSales();
                TempArticlesRepository.UpdateCategoryIds();

                //Copio a la tabla temporal de tiendas.
                TempStoresRepository.TruncateTable();
                TempStoresRepository.InsertDataFromTempSales();

                importerResults.Add(new ImporterResult
                {
                    ErrorType = ErrorType.Information,
                    Category = ResultCategory.TemporalTable,
                    Description = Messages.ImportacionATablaTemporalCorrecta
                });
            }
            catch (Exception ex)
            {
                errorCount++;

                string sheetName = excelCell != null ? excelCell.Worksheet.Name : string.Empty;
                string cellAddress = excelCell != null ? excelCell.Address : string.Empty;

                importerResults.Add(new ImporterResult
                {
                    Category = ResultCategory.TemporalTable,
                    Location = string.Format("Archivo: {0} - Hoja: {1} - Celda: {2}", fileName, sheetName, cellAddress),
                    Description = ex.Message,
                    ErrorType = ErrorType.Error,
                    Id = errorCount
                });
            }

            return importerResults;
        }

    }


}
